Re: Updating Multiple Columns in Multiple Rows

On Wed, 10 Oct 2007 16:00:03 -0700, kc <kecanniff_at_hotmail.com> wrote:

>-- The error on this attempt is:
>-- ORA-01407: cannot update ("PROSURV_0701"."TEST"."ID") to NULL
>-- Even though I'm specifying records in 1 of the correlating tables,
>it wants
>-- to hit every row and set non-matches to null
>UPDATE TEST T1
>SET ( ID ) = (
> SELECT T_OLD.ID
> FROM TEST T2
> JOIN T_OLD ON T2.ID = T_OLD.ID
> JOIN T_NEW ON T_OLD.ACC = T_NEW.ACC
> LEFT JOIN T_OLD T_OLD_2 ON T_OLD_2.ID = T2.ID
> WHERE
> T2.ID <> T_OLD.ID
> AND T_OLD_2.ID IS NULL
> AND T1.ID = T2.ID
>)
>WHERE T1.ID IN (
> SELECT ID FROM T_OLD
>);

This is a problem because the EXISTS does not refer to the join. It could be fixed by making the join (and only UPDATEing changed values) or adding NVL() to the subquery, and supplying the old value as the defult.

This should work:

UPDATE
        Test
SET

	Id =
	(
	 SELECT
		T_New.Id
	 FROM
		T_Old,
		T_New
	 WHERE
		T_Old.Id	= Test.Id
	   AND	T_Old.Acc	= T_New.Acc
	)
WHERE
	EXISTS
	(
	 SELECT
		*
	 FROM
		T_Old,
		T_New
	 WHERE
		T_Old.Id	= Test.Id
	   AND	T_Old.Acc	= T_New.Acc
	)

B. Received on Thu Oct 11 2007 - 08:52:19 CDT