Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update table a from table b where (conditions)

Evening all,

Actually, it's night. About 11pm. My brain is shutting down and I need a bit of help so I can finish and go home :)

I have two tables - table a and table b. I need to update a field in table a with the value from a field in table b when two other fields match. The tables don't have a unique id for each record :(

Basically, I want to do this:

update a
set importantField = 
(select b.importantfield
from b
where a.matchfield = b.matchfield
and a.matchfield2 = b.matchfield2
)
where a.matchfield = b.matchfield
and a.matchfield2 = b.matchfield2

Or at least... I think that's what I want to do...

Can someone help me out, please?

like image 609
Brian Beckett Avatar asked Jan 28 '10 23:01

Brian Beckett


People also ask

Can you write update query with WHERE condition?

The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

How do you update a table with multiple conditions?

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition.

How do you update a table based on condition in SQL?

We can update the table using UPDATE statement in SQL. The update statement is always followed by the SET command. The SET command is used to specify which columns and values need to be updated in a table.

Can you modify the rows in a table based on values from another table?

You can update an entire row in one table with values from a row in another table. Suppose that a master class schedule table needs to be updated with changes that have been made in a copy of the table. The changes are made to the work copy and merged into the master table every night.


2 Answers

You can do this via a join in the update:

Update a
Set a.importantField = b.importantField
From a Join b 
  On a.matchfield = b.matchfield
  And a.matchfield2 = b.matchfield2
like image 68
Nick Craver Avatar answered Nov 09 '22 15:11

Nick Craver


Use:

UPDATE TABLE_A
   SET importantField = (SELECT b.importantfield
                           FROM TABLE_B b
                          WHERE b.matchfield = matchfield
                            AND b.matchfield2 = matchfield2) 

SQL Server doesn't support table aliases on the table being updated, but the above is a correlated query - those fields without the table alias b attached will serve values from TABLE_A because it doesn't have an alias.

The only issue beyond that is if there are multiple b.importantfield values for records with the matching records to TABLE_A. Use:

UPDATE TABLE_A
   SET importantField = (SELECT TOP 1 
                                b.importantfield
                           FROM TABLE_B b
                          WHERE b.matchfield = matchfield
                            AND b.matchfield2 = matchfield2) 

..but you should use an ORDER BY as well or you'll get any random b.importantfield value.

like image 42
OMG Ponies Avatar answered Nov 09 '22 14:11

OMG Ponies