| Update problem. [message #372412] |
Fri, 09 February 2001 10:59  |
Karri Lahtela
Messages: 16
Registered: November 2000
|
Junior Member |
|
|
Hi
Script if following:
UPDATE PRICE
SET OLD_COST = (SELECT a.LIST_PRICE
FROM PURCHASE_SUPPLIER a, PRICE b
WHERE a.PART_NO = b.PART_NO
AND a.CURRENCY_CODE = 'FIM'
AND a.VENDOR_NO = '1232'
AND b.PART_NO in (SELECT a.PART_NO
FROM PURCHASE_SUPPLIER aa, PRICE bb
WHERE aa.PART_NO = bb.PART_NO
AND aa.CURRENCY_CODE = 'FIM'
AND aa.VENDOR_NO = '1232'
AND bb.new_part_cost IS NOT NULL))
Error message: ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
But SELECT -statement is right, I think. Pleace help me.
BR Karri
|
|
|
|
| Re: Update problem. [message #372414 is a reply to message #372412] |
Fri, 09 February 2001 11:36  |
Madhav
Messages: 14
Registered: November 1999
|
Junior Member |
|
|
What is the output of only Select statement when u run it separately?
If the no of rows > 1 you need to correct your select statement.
HTH
Madhav
|
|
|
|
| Re: Update problem. [message #372415 is a reply to message #372412] |
Fri, 09 February 2001 12:25 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
If you do not want to write a procedure...
Do this:
SELECT 'UPDATE PRICE
SET OLD_COST = '||a.LIST_PRICE||';'
FROM PURCHASE_SUPPLIER a, PRICE b
WHERE a.PART_NO = b.PART_NO
AND a.CURRENCY_CODE = 'FIM'
AND a.VENDOR_NO = '1232'
AND b.PART_NO in (SELECT a.PART_NO
FROM PURCHASE_SUPPLIER aa, PRICE bb
WHERE aa.PART_NO = bb.PART_NO
AND aa.CURRENCY_CODE = 'FIM'
AND aa.VENDOR_NO = '1232'
AND bb.new_part_cost IS NOT NULL);
Run this query and make sure you get the right data. Spool it to a file and Enter (run your query again). Spool off and @ your file.
Before you spool don't forget
SET FEEDBACK OFF
SET PAGESIZE 1000
If your LIST_PRICE is not NUMBER, surroung it with not one, but 3 '''. Like '''||LIST_PRICE||'';'
Good luck!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
