Re: Update with crosswalk table

The problem here is this the subqueries should have been a correlated subquery
like this
 UPDATE PS_PAYROLL_PIF_SF_at_HRTR Z
SET PAY_RATE_HR_SAL_SF =
  (SELECT A.HOURLY_RT
  FROM PS_EMPLOYEES_at_HRUT A
            , PS_XRF_EMPLID_SF_at_HRUT B,
   WHERE (B.EMPLID = Z.EMPLID)
   AND(B.EMPLID_SF = A.EMPLID)
   AND(A.HOURLY_RT is NOT NULL)
    )
WHERE EXISTS (
   SELECT 'X'
   FROM PS_EMPLOYEES_at_HRUT A, PS_XRF_EMPLID_SF_at_HRUT B,     WHERE (B.EMPLID = Z.EMPLID)
    AND(B.EMPLID_SF = A.EMPLID)
    AND(A.HOURLY_RT is NOT NULL)
     )
 ;

Hth,

Sybrand Bakker, Oracle DBA

arivlin_at_my-dejanews.com wrote in message <7hi7lf$shc$1_at_nnrp1.deja.com>...
>I tried, did not work for me. It updated all rows in the table, not the
>matching rows. if anyone knows how to do update on matching rows,
>please help.
>Alex
>In article <37260EC6.16F89117_at_cybercable.fr>,
> Bertrand LEBIN <blebin_at_cybercable.fr> wrote:
>> You have to specify which rows you want to update :
>> UPDATE
>> PS_PAYROLL_PIF_SF_at_HRTR SET PAY_RATE_HR_SAL_SF =
>> (SELECT
>> A.HOURLY_RT
>> FROM PS_EMPLOYEES_at_HRUT A, PS_XRF_EMPLID_SF_at_HRUT B,
>PS_PAYROLL_PIF_SF_at_HRTR Z
>> WHERE (B.EMPLID = Z.EMPLID)
>> AND(B.EMPLID_SF = A.EMPLID)
>> AND(A.HOURLY_RT is NOT NULL)
>> )
>> WHERE 1 IN (
>> SELECT 1
>> FROM PS_EMPLOYEES_at_HRUT A, PS_XRF_EMPLID_SF_at_HRUT B,
>PS_PAYROLL_PIF_SF_at_HRTR Z
>> WHERE (B.EMPLID = Z.EMPLID)
>> AND(B.EMPLID_SF = A.EMPLID)
>> AND(A.HOURLY_RT is NOT NULL)
>> )
>> ;
>>
>> Paul_Lei_at_ci.sf.ca.us a écrit :
>>
>> > Hi,
>> >
>> > I got stuck with an SQL Statment that maybe really obverious. Here
>it go:
>> >
>> > ********************* A *********************************** UPDATE
>> > PS_PAYROLL_PIF_SF_at_HRTR SET PAY_RATE_HR_SAL_SF =
>> >
>> > ********************* B *********************************** (SELECT
>> > A.HOURLY_RT
>> > FROM PS_EMPLOYEES_at_HRUT A, PS_XRF_EMPLID_SF_at_HRUT B,
>PS_PAYROLL_PIF_SF_at_HRTR Z
>> > WHERE (B.EMPLID = Z.EMPLID)
>> > AND
>> > (B.EMPLID_SF = A.EMPLID)
>> > AND
>> > (A.HOURLY_RT is NOT NULL));
>> > **********************************************************
>> >
>> > It would run on group B returning 104 row, which is the correct
>result. But if
>> > I add the line under group A it will bomb with 'multiple row
>return' error.
>> > Thanks
>> > Paul Lei
>> >
>> > -----------== Posted via Deja News, The Discussion Network
>==----------
>> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your
>Own
>>
>>
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
Received on Sat May 15 1999 - 00:35:17 CDT