copy a column from one table to another where table1.col = table2.col

Question

Suppose there are two tables which have the data mentioned in the insert query. There is no foreign key references between the two table.

create table uref.slave (
SLAVE_ID SMALLINT NOT NULL PRIMARY KEY,
DESC VARCHAR(20) 
);

INSERT INTO uref.SLAVE values (1, null)
INSERT INTO uref.SLAVE values (2, null)


create table uref.master (
MASTER_ID SMALLINT NOT NULL PRIMARY KEY,
SLAVE_ID SMALLINT,
DESC VARCHAR(20) 
);

INSERT INTO uref.MASTER values (1,1,'value1')
INSERT INTO uref.MASTER values (2,2,'value2')

Now I need a query which will copy uref.master.DESC into uref.slave.DESC based on uref.master.SLAVE_ID = uref.slave.SLAVE_ID.

This is a very common question. One example, see [SQL UPDATE from one table to another](http://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2), or search for answers. — WarrenT, Apr 29 '14 at 14:40

sion_corn · Answer 1 · 2014-05-01T15:01:05.910

1

UPDATE uref.SLAVE t1
    SET Desc = 
    (
        SELECT t2.Desc
        FROM uref.MASTER  t2
        WHERE t1.SLAVE_ID = t2.SLAVE_ID
    )
    WHERE EXISTS 
    ( 
        SELECT * 
        FROM uref.MASTER t2
        WHERE t1.SLAVE_ID = t2.SLAVE_ID
        AND NOT t1.Desc=t2.Desc
    )  
    AND t1.Desc IS NULL

edited May 01 '14 at 15:01

answered Apr 29 '14 at 13:40

sion_corn

3,043
8
39
65

I was trying this only but getting sql error.Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=update UREF.SLAVE set UREF.SLAVE.DESC = URE;BEGIN-OF-STATEMENT;, DRIVER=3.63.75 SQLState: 42601 ErrorCode: -104 – saptarshi Apr 29 '14 at 13:49
update uref.SLAVE set uref.SLAVE.DESC = uref.MASTER.DESC from uref.SLAVE inner join uref.MASTER on uref.SLAVE.SLAVE_ID = uref.MASTER.SLAVE_ID – saptarshi Apr 29 '14 at 13:50
I did not realize you were hitting a DB2 instance. Try my new query. – sion_corn Apr 29 '14 at 13:57
I have also tried this. The problem is that UREF.SLAVE.SLAVE_ID = 2 is only getting updated. SLAVE_ID = 1 does not have the corresponding value for DESC column. – saptarshi Apr 29 '14 at 14:03
try my latest edit. i hope it works - i don't have a DB2 instance I can test against. – sion_corn Apr 29 '14 at 14:20
gosh....no help...the result is the same. Only the last element is getting updated. Anyways..thanks for trying... – saptarshi Apr 29 '14 at 14:25
@saptarshi - There's no good reason for it to update only **one** record. The version is essentially standard ANSI-standard SQL and [should work on pretty much any RDBMS](http://sqlfiddle.com/#!15/1008c/4). @sion_corn - There _is_ at least one syntax error (`table2` vs `t2`), and there's at least one improvement possible (ie, adding `AND [Desc] IS NULL` so only null rows are updated), but this _is_ a viable answer. – Clockwork-Muse Apr 30 '14 at 09:56
@Clockwork-Muse thanks for the suggestion - I corrected the alias mistake. As for your suggested additional clause in the predicate - would I add that to both subqueries, or just the top? – sion_corn Apr 30 '14 at 13:19
Just the top; essentially you're restricting the subqueries from even being _run_, except to those which should make a difference. In some cases, this may even be required - that is, `Slave` has rows with different descriptions, but that shouldn't be updated (for whatever reason) – Clockwork-Muse Apr 30 '14 at 13:22
DB2 has different syntax. Perhaps the most noticeable differenc, we use `"` quotation marks around a name instead of `[]` square brackets, and this is when a name is reserved, uses special characters, or is case sensitive. (at least on DB2 for i) – WarrenT Apr 30 '14 at 15:32
It looks better now ;-) – WarrenT Apr 30 '14 at 16:32
Why do you have `AND t1.Desc IS NULL` on the first subselect? This means that you could satisfy the WHERE EXISTS for the UPDATE, but not return a value. Instead, I would put `AND NOT t1.Desc=t2.Desc`, and possibly add logic handling null values. – WarrenT Apr 30 '14 at 19:27
Sorry @sion_corn, he's right - I thought you meant the **top level** query. The way you have it now would actually change values that were already set to null... probably not what the op wants. The condition should have been added to the `WHERE` clause of the `UPDATE`, not to either of the subqueries. – Clockwork-Muse Apr 30 '14 at 22:03
I think I finally have it in its right form - again, no db2 instance I can test it against, but it looks right to me. Thanks for all of your help. – sion_corn May 01 '14 at 15:01

score 1 · Answer 2 · answered Apr 30 '14 at 19:43

The simplest solution may be to use MERGE.

MERGE INTO uref.SLAVE s
  USING uref.MASTER m
  ON (s.SLAVE_ID = m.SLAVE_ID)
  WHEN MATCHED 
  THEN UPDATE SET Desc = m.Desc

It could be refined to update only when there is a change to be made

MERGE INTO uref.SLAVE s
  USING uref.MASTER m
  ON (s.SLAVE_ID = m.SLAVE_ID)
  WHEN MATCHED
   and (   s.Desc <> m.Desc
       or (s.Desc is null and m.Desc is not null)
       ) 
  THEN UPDATE SET Desc = m.Desc

cyan · Answer 3 · 2014-04-29T14:01:36.333

if sql server, Try below sql: (recheck the table name and fields)

declare @urefSlave table (
SLAVE_ID SMALLINT ,
[DESC] VARCHAR(20) 
);

INSERT INTO @urefSlave values (1, null)
INSERT INTO @urefSlave values (2, null)


Declare @urefMaster table (
MASTER_ID SMALLINT,
SLAVE_ID SMALLINT,
[DESC] VARCHAR(20) 
);

INSERT INTO @urefMaster values (1,1,'value1')
INSERT INTO @urefMaster values (2,2,'value2')

select * from @urefMaster
select * from @urefSlave

update @urefSlave 
set [DESC] = b.[DESC]
from @urefSlave a inner join @urefMaster  b on a.SLAVE_ID = b.SLAVE_ID

select * from @urefSlave

REsult:

MASTER_ID SLAVE_ID DESC
--------- -------- --------------------
1         1        value1
2         2        value2



SLAVE_ID DESC
-------- --------------------
1        value1
2        value2

Updated

cannot help much in db2, because i don't have the tools to run the syntax but from this link db2 update help

you can modify an example in there to meet your requirement:

UPDATE EMPLOYEE EU
     SET (EU.SALARY, EU.COMM)
     =
   (SELECT AVG(ES.SALARY), AVG(ES.COMM)
     FROM EMPLOYEE ES
     WHERE ES.WORKDEPT = EU.WORKDEPT)
     WHERE EU.EMPNO = '000120'

Hope this help.

I am using db2. Tried the same but not working. Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=update UREF.SLAVE set DESC = ms.DESC;BEGIN-OF-STATEMENT;, DRIVER=3.63.75 SQLState: 42601 ErrorCode: -104 — saptarshi, Apr 29 '14 at 13:52
update uref.SLAVE set DESC = ms.DESC from uref.SLAVE sl inner join uref.MASTER ms on sl.SLAVE_ID = ms.SLAVE_ID — saptarshi, Apr 29 '14 at 13:53
thanks for you help....but it will not work for me as i need to copy all the datas and not a particular row. — saptarshi, Apr 29 '14 at 14:07

copy a column from one table to another where table1.col = table2.col

3 Answers3