BIK Information Services, Inc.

Oracle PL/SQL snippets
Building UPDATE statement based on a complex SELECT statement
Problem at hand:

  1. We have a table to be updated (table2update) and it has a primary key that consists of two columns pk_col1 and pk_col2. The rest of the columns are called col3, col4, ..., colN.
  2. We need to construct an update statement that updates multiple rows based on a result of a SELECT statement.
  3. Columns that make primary key aren't updated

Solution. Step 1. (Preparing future WHERE condition).
Create a SQL statement that selects rows you will need to have updated. In most cases this set of rows comes as a result of a join of the table2update and some other tables.

Solution. Step 1 (Preparing future WHERE condition). Result. 
	SELECT t2u.* FROM
		table2update t2u
		,other_table1,other_table2
	WHERE 
		{join_conditions}

Solution. Step 2 (Building future WHERE condition).
Change this SQL just a little bit by changing the list of columns and make it our SQL_1

Solution. Step 2 (Building future WHERE condition). Result - SQL_1. 
	SELECT t2u.rowid 
	FROM table2update t2u
	     ,other_table1,other_table2, ...
	WHERE 
		{join_conditions}

Step 3. (Creating future SET clause).
Create a SQL statement that contains as many rows as the one built in Step 2 and contains values to replace corresponding values from table2update. It must also include values equal to the primary key values for each updated row. Let's call it SQL_2.
Note: There is a good chance that this SQL will look very similar to SQL_1

Step 3. (Creating future SET clause). Result - SQL_2. 
	SELECT blah1 AS pk_col1, blah2 AS pk_col2
		, val3, val4, ..., valN
	FROM {list_of_tables}
	WHERE 
	     {join_cond}

Step 4. (Finishing up).
Now we are ready to create an UPDATE statement.

Step 4. (Finishing up). Result. 
	UPDATE table2update t2upd
	   SET (col3, col4, ... colN) = 
	   (
		SELECT val3, val4, ..., valN 
		FROM (
Paste SQL_2 here 
		) newval
		WHERE 
	   	    t2upd.pk_col1 = newval.pk_col1
	   	AND t2upd.pk_col2 = newval.pk_col2
	   )
	WHERE t2upd.rowid IN (
Paste SQL_1 here 
	)

We hope you found this snippet useful.
If so drop us a note to Boris Knizhnik  borisk@bikinfo.com.
We appreciate any links to this site as they show your appreciation and work as free ads.


Last modified on


Back to BIK Information Services Home page Back to BIK Information Services home page