Update a table with values in another

[craig.keightley]

I have the following table
tableA
column_a
column_x
column_y
column_z

tableB
column_x
column_y
column_z

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
tableB.column_z

any help would be grateful

Craig

[Bill Karwin]

<cr*************@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I have the following table
tableA
column_a
column_x
column_y
column_z

tableB
column_x
column_y
column_z

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
tableB.column_z

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.

Regards,
Bill K.