Re: Oracle update on a sub Query

mike wrote:

>Hi I'm been using M$ sql server forever and, now I've moved on to
>oracle and I can't seem to get this subquery stuff right. Can someone
>please help. Here is my terrible wrong query.
>
>
>Update SERVICE_TABLE_NEW stn SET
>(stn.MODEL_FULL ,stn.SCB,stn.SCS,stn.SCG ,stn.SCGRF,stn.OSB,stn.OSS,
>stn.OSG,stn.PB,stn.PS,stn.PG,stn.SCGBA,stn.SCWS,stn.SCWG,stn.SCWGBA
>,stn.SCWGRF,stn.OSWB,stn.OSWS,stn.OSWG,stn.SCWB,stn.FLAG )
>=( select
> t.MODEL_FULL , t.SCB, t.SCS ,t.SCG ,t.SCGRF ,t.OSB , t.OSS,t.OSG,
> t.PB , t.PS , t.PG , t.SCGBA , t.SCWS , t.SCWG , t.SCWGBA ,
>t.SCWGRF,
> t.OSWB, t.OSWS ,t.OSWG , t.SCWB , t.FLAG
> FROM SERVICE_TABLE_TEMP t
> where stn.MODEL = t.MODEL and stn.SAP_SALES_ORG =
>t.SAP_SALES_ORG )
> where stn.MODEL exists (select model from SERVICE_TABLE_TEMP) and
> stn.SAP_SALES_ORG exists (select SAP_SALES_ORG from
>SERVICE_TABLE_TEMP)
>
>
>Thanks in advance
>
>

The error message would be helpful but my sense here is that you are trying to use EXISTS as though it was a replacement for IN: It is not.

To use EXISTS you need a correlated subquery. Here are two simple examples that show the difference in usage.

SELECT srvr_id
FROM servers
WHERE srvr_id IN (

   SELECT srvr_id
   FROM serv_inst);

Which is roughly equivalent to what you have written. With EXISTS, below, note the correlation in the subquery to the primary statement.

SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)