SQL Update from One Table to Another Based on a Id Match

how to update a column from one Table to another based on a ID match

Note: I just used MySQL-syntax here, as you didn't specify. You need to adapt to your own dbms if you dont use MySQL.

There are two ways in which this can be accomplished.

Using a sub-query:

Using a sub-query is relatively straight forward:

update table2
set table2.dept = (
    select table1.dept 
    from table1
    where table1.id = table2.id
    limit 1
)
-- We need this, because if we don't have 
-- it then table2.dept will be null whenever 
-- there is no entry in table1 with the same id
where 0 < (select count(*) from table2 where table2.id = table1.id)
;

This is mostly handy if you only want to do this occasionally, but i can't really see why you would want to.

This is also not that efficient to do, if you have a big table.

Using a trigger:

I think using a trigger is nicer, as you just set it up and then it'll work its magic in the background:

delimiter //
create trigger updateDepartment
after update 
on table1 for each row
begin 
  -- new refers to the new row (with updated values)
  update table2 
  set dept = new.dept
  where id = new.id;
end; //

delimiter ;

You can read more about MySQL triggers here. There are more types of triggers, such as triggers which trigger when there is an insert, or a delete.

Update column with values from another table if ID exists in another table

Use an INNER JOIN not a subquery. This will implicitly filter to only rows where the related row is found:

UPDATE T1
SET [Value] = T2.Value
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.ID = T2.ID;

db<>fiddle

postgreSQL update from one Table to another based on a ID match

The target table of an update statement should never be repeated in the from clause

So I think you want this:

UPDATE sites s
  SET cgid = c.gid 
FROM counties c 
where c.name = s.county;

This assumes that counties.name and sites.county are both unique.

update one table with data from another

For MySql:

UPDATE table1 JOIN table2 
    ON table1.id = table2.id
SET table1.name = table2.name,
    table1.`desc` = table2.`desc`

For Sql Server:

UPDATE   table1
SET table1.name = table2.name,
    table1.[desc] = table2.[desc]
FROM table1 JOIN table2 
   ON table1.id = table2.id

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

SQL update from one Table to another based on a ID match IN db2

I'm pretty sure (although I've not used DB2 in a while) that DB2 still does not support joins in update statements, so you'll need to use MERGE;

Something like this (freehanding it since I don't have DB2 available, so may be slightly off);

MERGE INTO Sales_Import si
USING (SELECT AccountNumber, LeadID FROM RetrieveAccountNumber) ra
ON (si.LeadID = ra.LeadID)
WHEN MATCHED THEN
 UPDATE SET AccountNumber = ra.AccountNumber

---

Related Topics