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 Saturday, 26-Feb-2005 08:08:10 EST


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