| Update statement [message #398113] |
Wed, 15 April 2009 05:00  |
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  |
cookiemonster
Messages: 13965
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 |
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 |
cookiemonster
Messages: 13965
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 |
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 #398171 is a reply to message #398160] |
Wed, 15 April 2009 08:33 |
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 |
cookiemonster
Messages: 13965
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
