Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Update a table with values in another
January 18, 2006, 9:51 am
rate this thread
How do I update the rows of tableA that match rows of tableB (all rows
of tableB are unique)
What I want to acheive is a check against tableA.column_x with
tableB.column_x, if they match, then update the row in tableA.column_y
with the value of tableB.column_y and tableA.column_z with
any help would be grateful
Re: Update a table with values in another
MySQL offers a SQL extension that permits references to multiple tables in
UPDATE and DELETE statements (don't try this on any other RDBMS).
UPDATE tableA, tableB
SET tableA.column_y = tableB.column_y, tableA.column_z = tableB.column_z
WHERE tableA.column_x = tableB.column_x
Without this feature, what I've done in the past is run a SELECT based on
the join of tableA and tableB, and add literal text so that the output of
the query is a series of UPDATE statements. You can then run the output as
a SQL script.
SELECT CONCAT('UPDATE tableA SET column_y = ',
b.column_y, ', column_z = ', b.column_z,
' WHERE column_x = ', a.column_x, ';')
FROM tableA AS a INNER JOIN tableB AS b
ON a.column_x = b.column_x;
This technique is kind of meticulous; you have to watch your commas and
quotes very carefully. It gets even more hairy if the columns are strings
instead of integers, because they require escaped quotes inside the strings.
Thank goodness for the multi-table updates.
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum