Update a table with values in another

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have the following table


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

Quoted text here. Click to load it

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.

Bill K.

Site Timeline