"Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005 05:30:51 AM:
> Hi, > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the > results into the column of a third. This appears to be harder than I > realised with this version of mysql and I am banging my head against a > wall. Please Help! > ok first query. [snip] > any ideas? > > Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the "thing to be updated". Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET <*** see note***> WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan.
