| update statement with embedded select -- wierd results? [message #21329] |
Fri, 26 July 2002 07:49  |
Son Nguyen
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Hello,
I have two tables, T1 and T2.
Both tables have a field called ID and PartNum.
T1 has 20 records, T2 has 10 records.
SQL:
UPDATE T1 SET T1.PartNum =
(SELECT T2.PartNum FROM T2 WHERE T2.ID = T1.ID)
SQL*Loader returns that 20 records were updated, but there are only 10 records that match on the ID join. I looked at the table and it updated the PartNum on T1 correctly on the matching joins, but the ones it DID NOT match it SET IT TO NULL. I usually use MS SQL Server which allows you to do INNER JOIN tables and it would only update the records where the ID matched. Any thoughts?
Thanks.
Son Nguyen
|
|
|
|
|
|
|
|
| Re: update statement with embedded select -- wierd results? [message #21336 is a reply to message #21329] |
Fri, 26 July 2002 08:27  |
Son Nguyen
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Hello,
I tried that with my real scenario and it get an error of ERROR at line 2: ORA-01427: single-row subquery returns more than one row
Here is the real SQL statement, basically the same but with more joins:
UPDATE TEMP SET TEMP.PartID =
(SELECT Part.PartID FROM Part, TEMP WHERE
Part.PartNum = TEMP.PartNum AND Part.PartNumExt = TEMP.PartNumExt AND Part.UOM = TEMP.UOM AND Part.SupplierID = 1)
Thanks again.
|
|
|
|
| Re: update statement with embedded select -- wierd results? [message #21339 is a reply to message #21336] |
Fri, 26 July 2002 09:36 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The behavior you saw (non-matching rows were updated to NULL) is the expected behavior. To preserve the data in rows where there is no match, you need to either use a WHERE EXISTS clause or NVL on the result. In either case, do not include another reference to the base table (TEMP) in the subquery - it should be a correlated subquery instead:
update temp t
set partid = (select p.partid
from part p
where p.partnum = t.partnum
and p.partnumext = t.partnumext
and p.uom = t.uom
and p.supplierid = 1)
where exists
(select null
from part p
where p.partnum = t.partnum
and p.partnumext = t.partnumext
and p.uom = t.uom
and p.supplierid = 1);
Alternatively:
update temp t
set partid = nvl((select p.partid
from part p
where p.partnum = t.partnum
and p.partnumext = t.partnumext
and p.uom = t.uom
and p.supplierid = 1), partid);
Option 2 does update each row but sets the partid to the old value instead of NULL when no match is found. This "fake" update may not be desirable in certain situations (rollback size, triggers, etc.), but it does avoid the dual lookup.
With either approach, the subquery must return at most a single row.
|
|
|
|
| Re: update statement with embedded select -- wierd results? [message #21344 is a reply to message #21336] |
Fri, 26 July 2002 13:32 |
Son Nguyen
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Thanks for the help! I got around it this way:
update temp t set partid =
(select p.partid from part p
where p.partnum = t.partnum and p.partnumext = t.partnumext and p.uom = t.uom and p.supplierid = 1)
where (t.partnum, t.partnumext, t.uom) in (select partnum, partnumext, uom from part)
you did something similiar but used where EXISTS instead.. which one is better in this case?
thanks again!
|
|
|
|
| Re: update statement with embedded select -- wierd results? [message #21346 is a reply to message #21344] |
Fri, 26 July 2002 14:35 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You have extremely small tables, so the difference is irrelevent, but on bigger tables, the WHERE IN approach would require an index on the base table (TEMP) AND an index on the lookup table.
The WHERE EXISTS approach requires just an index on the lookup table (PART).
Also, your approach theoretically could fail if there was a matching entry in part based on partnum, partnumext, and uom - but the supplierid <> 1. I still prefer the WHERE EXISTS approach, but with your example, the main WHERE clause should have been:
where (partnum, partnumext, uom, 1) in (select partnum, partnumext, uom, supplierid) from part); |
|
|
|
| Oracle query [message #21349 is a reply to message #21329] |
Sat, 27 July 2002 03:33 |
yuvaraj
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
In the subquey no need to mention the t2 table in from
statment
then
I have a doubht
what is the query to bring the accumulated salary
for example
1 radha 100
2 raghv 200
3 arjun 400
If I paid 400 Rs totally then it shows Like this
1 radha 100 100
2 raghv 200 200
3 arjun 400 100
|
|
|
|
| Oracle query [message #21894 is a reply to message #21329] |
Sun, 08 September 2002 20:36 |
B.Raghavan
Messages: 1
Registered: September 2002
|
Junior Member |
|
|
Dear Sir,
I have the basic doubts :
1) How to create the duplicate table (which need to contain same table structure and data's also)
2) What is the use SID in tnsnames.ora
3) Suppose database is remote side (we are accessing the database from remote through network)
what are all configuration need to be done.
i think surely you will response asap.
thanks and regards
Raghavan.B
|
|
|
|
| Re: update statement with embedded select -- wierd results? [message #21940 is a reply to message #21329] |
Wed, 11 September 2002 16:47 |
Petr Pivonka
Messages: 1
Registered: September 2002
|
Junior Member |
|
|
My suggestion is quite late for, but I solve the same problem just now and following statement works reliably:
UPDATE tab1, tab2
SET tab1.col1 = tab2.col1
WHERE tab1.col2 = tab2.col2
I didn't get any NULL settings of tab1.col1 in the case of non-matching joins. I only suppose redundant processing when tab2.col2 has duplicity.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
