Systems Engineering and RDBMS

Update data in one table with data from another table

Posted by decipherinfosys on January 31, 2007

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:
I.    TableA
a    b    c    d
1    x    y    z
2    a    b    c
3    t    x    z

II.    TableB
a1    b1    c1    d1    e1
1    x1    y1    z1    40
2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:

a    b    c    d
————————————
1     x          y           z
2     a1        b1         c1
3     t           x           z

SQL Server:

UPDATE TABLEA
SET     b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:

a    b    c    d
————————————
1     x          y           z
2     a1        b1         c1
3     t           x           z

DB2 LUW:

–Same as Oracle–

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Results after the update:

a    b    c    d
————————————
1     x          y           z
2     a1        b1         c1
3     t           x           z

NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon.  If it is incorrect, then you can get wrong results.  The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:

a    b    c    d
————————————
1     Null      Null      Null
2     a1        b1         c1
3     Null     Null      Null

About these ads

5 Responses to “Update data in one table with data from another table”

  1. CSIS Blogs said

    Updating a table with data from another in SQL server 2005…

    I was helping my class move a project from a semi-normalized design to a fully normalized design. This required splitting the un-normalized table into multiple tables. Rather than re-create new test data, we took the live project data and decided to ma…

  2. [...] SSIS Packages with SQL Server AgentThe user is not associated with a trusted SQL Server ConnectionUpdate data in one table with data from another tableDisable/Enable Foreign Key and Check constraints in SQL ServerTemporary Tables – MS SQL [...]

  3. [...] Posts Update data in one table with data from another tableFinding the Port Number for a particular SQL Server InstanceDifferences between different SQL Server [...]

  4. [...] Posts Update data in one table with data from another tableFinding the Port Number for a particular SQL Server InstanceDeploying SSIS Packages in SQL Server [...]

  5. [...] nkshirsa I don't know if you ever resolved this, but I did find a reference for Updating Oracle where there was a joined table. see:http://decipherinfosys.wordpress.com…another-table/ [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: