CODEWITHSUNDEEP
databasejoinsql-updatedb2
evkwan
evkwan

Reputation: 703

UPDATE JOIN statement for DB2

I am using DB2, and am a beginner in SQL. I have two tables here:

Table1:

ID | PageID 
------------
1  |   101  
2  |   102
3  |   103
4  |   104

Table2:

ID | SRCID | PageID
--------------------
1  |  2    | 179
2  |  3    | 103
3  |  3    | 109

Table2 and Table1 have different number of records. Table2.SCRID corresponds to Table1.ID. I would like to update the PageID in Table2 to follow what is stated in PageID of Table1, based on the SRCID. My end result of Table2 should be:

ID | SRCID | PageID
--------------------
1  |  2    | 102
2  |  3    | 103
3  |  3    | 103

How do I do this in SQL for DB2?

I tried:

UPDATE table2
  SET PageID = (SELECT t1.PageID from table1 as t1 join table2 as t2
      WHERE t2.SCRID = t1.ID);

But the above doesn't work as I get:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

The problem here is there is no unique column for me to join such that each column gets a unique result..or so it seems to me. Please help? :(

Upvotes: 2

Views: 14962

Answers (2)

oxwilder
oxwilder

Reputation: 767

As a SQL Server loyalist, I've been struggling with DB2's seeming inability to update a table with information from another table--the update with join that's so easy in SSMS.

I finally discovered a workaround that functions perfectly instead: the MERGE statement. I usually find IBM's support documents impenetrable, or at least not friendly reading, but the explanation at their MERGE website was actually quite clear: https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/sqlp/rbafymerge.htm

Hope this helps you as much as it did me.

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

Try this:

UPDATE table2
SET table2.PageID = 
    (SELECT t1.PageID
    FROM table1 t1
   WHERE t1.id = table2.SCRID)
WHERE EXISTS(
    SELECT 'TABLE1PAGE'
    FROM table1 t1
    WHERE t1.id = table2.SCRID)

I've added EXISTS clause to prevent NULL assignment to PageID of table2

Upvotes: 1

Related Questions