Join in Update query

[bheemsen]

Hi,

The following query works fine in SQL Server and I would like to achieve the same in Oracle 9i. It does not work in Oracle. Can any one help who knows the equivalent of this in Oracle ?

UPDATE WEB_FSP_DATA a, WEB_DA_CERTIFICATION b
SET a.address_qualified_date = SYSDATE,
a.da_completed_id = b.da_certified
WHERE a.wire_cntr_cd = b.wire_cntr_cd
AND a.da_cd = b.da_cd
AND b.da_certified = 'Y'
AND a.da_completed_ind IS NULL;

Thanks.
-Bheem

 

[SantaMufasa]

Bheem,

Could you please confirm one item for us:

Should "a.da_completed_ind" actually be "a.da_completed_id" (or vice versa)?...Or do you really have those two different columns in your WEB_FSP_DATA table?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bheemsen]

I am sorry, that was a typo.

da_completed_id should be da_completed_ind.

 

[SantaMufasa]

That seems much more reasonable, Bheem.

Also, as a performance improvement, since "b.da_certified = 'Y'" in all cases where an update occurs, you can simplify your update statement by just saying:

"a.da_completed_ind = 'Y'" instead of
"a.da_completed_ind = b.da_certified", right?

Here, then, is code that will do, in Oracle, what you coded for SQL Server:

UPDATE WEB_FSP_DATA a
SET a.address_qualified_date = SYSDATE,
-- a.da_completed_ind = b.da_certified
a.da_completed_ind = 'Y' -- performance improvement
where exists (select 'Bheem'
                from WEB_DA_CERTIFICATION b
	       WHERE a.wire_cntr_cd = b.wire_cntr_cd
                 AND a.da_cd = b.da_cd
                 AND b.da_certified = 'Y')
  AND a.da_completed_ind IS NULL
/

In the above, if you have never used the "exists" operator, it simply means, "UPDATE only rows in the outer table that correlate with matching rows in the inner query. Then the last "AND" is part of the outer UPDATE's WHERE clause (not part of the inner correlated subquery), to limit the updates to just the outer table's rows that have NULL in the da_completed_ind column.

Does this make sense? Regardless, I guarantee this statement does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[bheemsen]

Thanks. That is fantastic. Your explantion is good and easy to understand.

-Bheem