Update data in one table with data from another table

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

Dead Client Detection (DCD) in Oracle

A connection in Oracle might be one of many types — most popular begin DEDICATED server and SHARED server. We will discuss the “DEDICATED” mode only in this post.

Sid and Serial# jointly identify a record in the v$session view. Suppose the client application makes a connection and starts a transaction and holds locks and just then, the network connectivity is lost or a power-failure occurs on the machine from where the client application is running from (assuming that it is not running on the DB Server), in all such scenarios, the client is not able to communicate to the DB that the connection is going away and hence leaves hung sessions and if those sessions were holding locks, open locks on the DB. Such sessions are called as orphaned sessions. PMON has no visibility into these and hence it is not able to clean this session up (nor it’s resources).

Issues with Orphaned Sessions:

Orphaned sessions use server resources, and may have open cursors, temporary tables, or locks. These locks may block other connections from performing useful work, and can sometimes be the result of a major “pile up” of locks. In severe cases, it can appear that Oracle has stopped working.

Resolutions:

You can enable dead client detection (DCD) in the sqlnet.ora file on the DB Server (not on the client application machines). It is done by setting the SQLNET.EXPIRE_TIME setting in the sqlnet.ora file on the DB Server. The time specified is in minutes and what it does is that it sends a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This setting is intended for the database server, which typically handles multiple connections at any one time.

Limitations on using this terminated connection detection feature are:

* It is not allowed on bequeathed connections.
* Though very small, a probe packet generates additional traffic that may downgrade network performance. So, if you put a lower number for the time parameter, it can create issues for you.
* Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in downgrading network performance.

The advantage of setting this parameter is that you won’t have “dead” clients hogging resources on your DB.