How do I use exists in a subquery

[tpbjr]

I am trying run this query statement but it keeps producing the following error message:

where exists (select * from vendor_temp
unknown command beginning "where exis..." - rest of line ignored.
SQL> where a.vendor_cd = vendor_cd)B
unknown command beginning "where a.ve..." - rest of line ignored.

Here is the PL/SQL

update vendor a
set vendor_name = (select vendor_name
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
modified_by = (select modified_by
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
last_mod_dt = (select last_mod_dt
from vendor_temp b
where a.vendor_cd = b.vendor_cd)

where exists (select * from vendor_temp
where a.vendor_cd = b.vendor_cd);



I need to update the vendor_name, modified_by and last_mod_dt fields from the vendor table if the vendor_cd exists in the vendor table and the vendor_temp table



I would deeply appreciate your help


Thank you for all your help

http://www.besware.com

Tom

 

[PHV]

Have you tried this ?
update vendor a inner join vendor_temp b on a.vendor_cd = b.vendor_cd
set a.vendor_name = b.vendor_name
, a.modified_by = b.modified_by
, a.last_mod_dt = b.last_mod_dt
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tpbjr]

I tried that and received the error listed below.
This is PL/SQL not MS SQL.

update vendor a inner join vendor_temp b on a.vendor_cd = b.vendor_cd
*
ERROR at line 1:
ORA-00971: missing SET keyword

It looks like it requires SET after the table name.

Maybe this help help you continue to help me.
Thanks again.

Thank you for all your help

http://www.besware.com

Tom

 

[PHV]

Don't know PL/SQL, but maybe:
update vendor a
set vendor_name = (select vendor_name
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
modified_by = (select modified_by
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
last_mod_dt = (select last_mod_dt
from vendor_temp b
where a.vendor_cd = b.vendor_cd)
where a.vendor_cd in (select vendor_cd from vendor_temp)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

This is PL/SQL not MS SQL

then you might want to consider posting your question in the oracle forum

this is the ANSI SQL forum

UPDATE statements are notoriously non-standard across the various vendors' database systems

rudy

http://r937.com/

SQL Consulting

 

[tpbjr]

I took your suggest and went to the Oracle forum. The problem was I had a blank line before the where clause and in the subquery I didn't give the vendor_temp table an alias. I didn't find that out until I removed the blank line. Anyway here is the fix:

update vendor a
set vendor_name = (select vendor_name
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
modified_by = (select modified_by
from vendor_temp b
where a.vendor_cd = b.vendor_cd),
last_mod_dt = (select last_mod_dt
from vendor_temp b
where a.vendor_cd = b.vendor_cd)
where exists (select * from vendor_temp c
where a.vendor_cd = c.vendor_cd);


I thought it would be nice to pass this information on.

Thanks again everybody.


Thank you for all your help

http://www.besware.com

Tom