Nate Bunnell Nate Bunnell - 1 year ago 49
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

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 Source

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 

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download