RE: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty wrote: 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. [snip] Shawn Green wrote: 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. [snip] Michael wrote: [snip] From the manual page Shawn cites: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. So, yes, it's a version issue. So, to return to my original question ... Is there a workaround or a way of updating from a joined query in 4mysql? I thought perhaps creating a temporary table, but even this doesn't seem to work? There MUST be a way of doing this ... Its an administrative nightmare. Regards, Brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN QUERY - UPDATE ... help?!
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. - 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' - Now I want to update table 3 (links_DB) using page_elements.link_ID=links_DB.link_ID usual update query doesn't seem to work in this circumstance .. any ideas?
Re: JOIN QUERY - UPDATE ... help?!
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. - 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' - Now I want to update table 3 (links_DB) using page_elements.link_ID=links_DB.link_ID usual update query doesn't seem to work in this circumstance .. 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: JOIN QUERY - UPDATE ... help?!
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.
Re: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty wrote: 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. snip Shawn Green wrote: 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: snip Brendan Gogarty wrote: 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. From the manual page Shawn cites: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. So, yes, it's a version issue. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY - UPDATE ... help?!
Multi Table UPDATES are first supported in 4.0.x -Original Message- From: Brendan Gogarty [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 12:16 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: JOIN QUERY - UPDATE ... help?! 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
