Nate Bunnell Nate Bunnell - 5 months ago 10
MySQL Question

Update rows from data in another table using corresponding unique ID's

Total newbie, trying to figure it out. wrote a bad query and deleted a majority of the first and last names in my master customer table. I have the names in different tables but can t seem to figure out how to insert just the names

first
and
last
into my master table according to each unique id.

This is what I have tried so far and totally in over my head.

UPDATE `Master_table` SET `first` = (SELECT `first` FROM `table_1`) WHERE `id` IN(SELECT `id` FROM `table_1`);


Any help and mercy would be appreciated

Answer

Sorry to hear about the deletions. I can see where you are going with your query, but correlated subqueries are not allowed in an UPDATE statement (although you didn't correlate here, which is where the problems really started). Instead:

UPDATE Master_table, table_1 
SET master_table.first = table_1.first, 
    master_table.last = table_1.last 
WHERE master_table.id = table_1.id;

You can dive in deeper on MySQL's UPDATE help page. But the basic idea is to list the tables involved in the UPDATE clause, and their relation (like a join) in the WHERE clause. Then your SET is pretty straightforward.