Posted on by Dengineer PC

There are a few different ways to update a “source table” by joining it to another table.

 

ANSI/ISO:

Transact-SQL
0
1
2
3
4
5
6
7
8
9
10
11
12
 
update ud
  set assid = (
               select sale.assid
                 from sale
                where sale.udid = ud.id
              )
where exists (
               select *
                 from sale
                where sale.udid = ud.id
              );
 

 

MySQL:

MySQL
0
1
2
3
4
5
 
update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid
 

 

SQL Server:

Transact-SQL
0
1
2
3
4
5
6
 
update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid
 

 

Oracle:

Oracle PL/SQL
0
1
2
3
4
5
6
7
8
9
 
update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid
 

 

Share This Via: