Home » SQL & PL/SQL » SQL & PL/SQL » Update statement (10.2.0.3.0)
Update statement [message #398113] Wed, 15 April 2009 05:00 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written UPdate statement. But I am getting "Single row subquery returns more than one row error. Can any one please correct the query.
 UPDATE ods.gor_goldrush_postpaid a
   SET (a.hoover_flag, a.b49n) =
          (SELECT NVL (hoover_flag, hoover_flag), NVL (b49n, a.b49n)
             FROM (SELECT CASE
                             WHEN TRUNC
                                     (ADD_MONTHS (loyalty_date,loyalty_period)- SYSDATE) < 304
                                THEN 1
                             WHEN (TRUNC(ADD_MONTHS(loyalty_date,loyalty_period)- SYSDATE ) IS NULL
                                   AND (SYSDATE - TO_DATE(contract_date, 'YYYYMMDD')) > 91.2)
                                THEN 1
                             ELSE 99
                          END hoover_flag,
                          CASE
                             WHEN TRUNC(ADD_MONTHS(loyalty_date,loyalty_period)- SYSDATE) < 121.6
                                tHEN 1
                             WHEN (TRUNC(ADD_MONTHS(loyalty_date,loyalty_period)- SYSDATE) IS NULL
                                   AND(SYSDATE - TO_DATE (contract_date, 'YYYYMMDD')) > 91.2)
                                THEN 1
                             ELSE 99
                          END b49n
                     FROM ods.gor_goldrush_postpaid a
                    WHERE a.tariff_code IN (169, 135, 136)))
                        );


Thank you,
Re: Update statement [message #398117 is a reply to message #398113] Wed, 15 April 2009 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
No we can't.
You can - by rewriting your select to only return one row. We aren't going to know how to do that because we don't know your data model.
You might try correlating the select to the update but for that to work you really need to give the two instances of gor_goldrush_postpaid different aliases.
Re: Update statement [message #398123 is a reply to message #398117] Wed, 15 April 2009 05:34 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I have written following PL/SQL logic. using this can you please help me out to write UPDATE statement.

DECLARE
   v_hoov_***   gor_gold_post.hoov_flg%TYPE;
   v_b49n          gor_gold_post.b49n%TYPE;
 
   CURSOR c
   IS
      SELECT bs_id, loyalty_date, loyalty_period, contract_date
        FROM gor_gold_post
       WHERE tariff_code IN (169, 135, 136);
BEGIN
   FOR rec IN c
   LOOP
      IF    (TRUNC (ADD_MONTHS (rec.loyalty_date, rec.loyalty_period)
                    - SYSDATE) < 304
            )
         OR (    TRUNC (  ADD_MONTHS (rec.loyalty_date, rec.loyalty_period)
                        - SYSDATE
                       ) IS NULL
             AND (SYSDATE - TO_DATE (rec.contract_date, 'YYYYMMDD')) > 91.2
            )
      THEN
         v_hoov_flg := 1;
      ELSE
         v_hoover_flag := 99;
      END IF;
 
      IF    (TRUNC (ADD_MONTHS (rec.loyalty_date, rec.loyalty_period)
                    - SYSDATE) < 121.6
            )
         OR (    TRUNC (  ADD_MONTHS (rec.loyalty_date, rec.loyalty_period)
                        - SYSDATE
                       ) IS NULL
             AND (SYSDATE - TO_DATE (rec.contract_date, 'YYYYMMDD')) > 91.2
            )
      THEN
         v_b49n := 1;
      ELSE
         v_b49n := 99;
      END IF;
 
      UPDATE gor_gold_post
         SET hoov_flg = v_hoov_flg,
             b49n = v_b49n
       WHERE bs_id = rec.bs_id AND tariff_code IN (169, 135, 136);
 
      COMMIT;
   END LOOP;
END;
Re: Update statement [message #398136 is a reply to message #398117] Wed, 15 April 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 15 April 2009 11:16
We aren't going to know how to do that because we don't know your data model.



Re: Update statement [message #398157 is a reply to message #398123] Wed, 15 April 2009 07:19 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@user71408,
user71408 wrote on Wed, 15 April 2009 16:04

      UPDATE gor_gold_post
         SET hoov_flg = v_hoov_flg,
             b49n = v_b49n
       WHERE bs_id = rec.bs_id AND tariff_code IN (169, 135, 136);



Relate the outer table in your Update Statement with the one you have used in the subquery using bs_id as you gave in the above query.

This might help.

Regards,
Jo
Re: Update statement [message #398160 is a reply to message #398113] Wed, 15 April 2009 07:35 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It might, but if bs_id is unique then I have to wonder why the update is checking tariff_code.
Re: Update statement [message #398171 is a reply to message #398160] Wed, 15 April 2009 08:33 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@cookiemonster,

Based on the PL/SQL Logic OP gave and if it is working correctly, that condition might be applicable to the table which he is updating and not to the subquery. He is restricting the updates of the table to records matching the following condition
tariff_code IN (169, 135, 136)


I missed your first reply. I see you had already given him the hint of correlating the tables.

Another of my concern was what is the following NVL Function going to do?
NVL (hoover_flag, hoover_flag)


I feel a simple update(one without any subqueries) will work fine for OP.
UPDATE gor_gold_post
         SET hoov_flg = CASE .....
WHERE (conditions)....

I might have terribly misunderstood the requirement of OP.

Regards,
Jo

[Updated on: Wed, 15 April 2009 08:35]

Report message to a moderator

Re: Update statement [message #398175 is a reply to message #398171] Wed, 15 April 2009 09:22 Go to previous message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
joicejohn wrote on Wed, 15 April 2009 14:33

I feel a simple update(one without any subqueries) will work fine for OP.
UPDATE gor_gold_post
         SET hoov_flg = CASE .....
WHERE (conditions)....




Looking at the PL/SQL more closely I suspect you're right, but we're both just guessing.
Bottom line - if the OP wants to change this PL/SQL to a single SQL statement then he needs to really understand what rows he's updating to what values under what circumstances. I'm guessing the OP doesn't really understand all that because if he did then I don't see how he could get this error.

And the PL/SQL isn't the best specification because the IN on the update looks suspiciously like extraneous code.

joicejohn wrote on Wed, 15 April 2009 14:33

Another of my concern was what is the following NVL Function going to do?
NVL (hoover_flag, hoover_flag)




didn't even notice that - I assume it's meant to be the flag from the update and the flag from the sub-query. Won't work obviously which is why the OP really needs to use aliases properly.
Previous Topic: Update query taking long time
Next Topic: ROLE ASSIGNING (merged 3)
Goto Forum:
  


Current Time: Fri Dec 02 22:34:13 CST 2016

Total time taken to generate the page: 0.22735 seconds