SQL question: update table with values from another table

[SashaBuilder3]

Hi everybody,


I am trying to update a table with values from another table.
Here is a simplified scenario of what I wanna do:


Table t1:

fld1 fld2
---------
a 5
b
c 3
d
e 7
f


Table t2:

fld1 fld2
---------
b 2
d 1
f 3


I need to transfer values of t2.fld2 to corresponding cells of t1.fld2.

I issued the following SQL statement:


UPDATE t1
SET fld2 = (SELECT fld2
FROM t2
WHERE t2.fld1 = t1.fld1
)
WHERE fld1 IN (
SELECT fld1
FROM t2
)


However it the query doesn't work, I am getting an error message:

ORA-01427: single-row subquery returns more than one row



Can anyone help in writing a correct query?



Thanks,

Alexandre

 

[srishan]

UPDATE t1
SET t1.fld2 = (SELECT t2.fld2 FROM t2
               WHERE t2.fld1 = t1.fld1
               )
WHERE EXISTS
             (SELECT 'X' FROM t2
              WHERE t2.fld1 = t1.fld1
              );

Thx,
SriDHAR

 

[SashaBuilder3]

srishan,


Thanks for your response. First of all, what is 'X' in the last SELECT clause? Anyway, I tried this but got the same error message.

More ideas?


Thanks,

Alexandre

 

[karluk]

Your problem is that the select from t2 isn't returning a unique row. Therefore Oracle doesn't know which value of t2.fld2 to use when updating t1.

You are the one who has to decide how to refine the query to get a unique value. Something like

UPDATE t1
SET fld2 = (SELECT max(fld2)
FROM t2
WHERE t2.fld1 = t1.fld1
)
WHERE fld1 IN (
SELECT fld1
FROM t2
)

should work, but of course we can't tell if that's the right value to select.

 

[SashaBuilder3]

karluk,


I know how to solve this problem with opening a cursor, but I thought there is a straight SQL way.



Thanks anyway!

Alexandre

 

[srishan]

Sasha,

'X' means nothing, you can put 1, *, NULL in it's place if you want. To your error, I kinda did not read about error problem from the very first posting of yours, sorry.
Here is what you can do,

UPDATE t1
SET t1.fld2 = (SELECT distinct t2.fld2 FROM t2
               WHERE t2.fld1 = t1.fld1
               )
WHERE EXISTS
             (SELECT 'X' FROM t2
              WHERE t2.fld1 = t1.fld1
              );

Thx,
Sri

 

[SashaBuilder3]

srishan,


DISTINCT made little change, I got the same error message.

Hmmmm?


Thanks again!

Alexandre

 

[sem]

UPDATE t1
SET fld2 = (SELECT fld2
FROM t2
WHERE t2.fld1 = t1.fld1 and rownum=1)

 

[SashaBuilder3]

Sem,

rownum=1 made it!

Thanks alot!!!


Alexandre

 

[srishan]

1. Sem's query is not complete with out EXISTS clause.

** You must have EXISTS clause to avoid NULL values for the rows not matching on t2.fld1 = t1.fld1 **

2. Distinct is meant to avoid duplication if there are two matching rows. If you use rownum <= 1 or rownum= 1, you are saying that you would not care which value you get from many values for the matching rows on t2.fld1 = t1.fld1.
This one strictly business driven, what ever your business logic says.

If this is the case, whey didn't karluk's query work? That should work.

Thx,
SriDHAR

 

[sem]

EXISTS clause is not necessary, because the field in row without matches will be nullified not raising an error. Chosing from max(), min() or other single-row function depends on requirements. But untill they're NOT SPECIFIED, choosing first row saves resources at least (STOPKEY).

 

[karluk]

I think srishan is right. Sem's update would set the row (a,5) in t1 to (a, null). That's different than SashaBuilder3's original attempt, which leaves it untouched. You don't get an error, but maybe you don't get what you want either.

I'm fairly sure that my query will work but, as I said earlier, it's not clear whether it's what SashaBuilder3 wants to do when he gets multiple matching values. Perhaps he wants the minimum instead of the maximum, the average, or some other value.

 

[SashaBuilder3]

Gentlemen,


Hardly can I add anything to your discussion, my experience is too little. But the discussion is definitely fruitful since my procedure works.

My gratitude to all of you!!!


Alexandre

 

[srishan]

Table t1:

fld1 fld2
---------
a   5
b   
c   3
d
e   7
f


Table t2:

fld1 fld2
---------
b   2
d   1
f   3

If you look at table t1, your code would update all the rows, it would update the desired rows(b, d, f) okay, but puts NULL in all other rows. In effect rows(a, c, e) would lose their fld2 values(5, 3, 7). This should not be OKAY.
Thx,
SriDHAR

 

[srishan]

Guys,

I am new to this form. I am trying to fit in with my contributions, please do not take my comments subjective.

Thx,
Sri

 

[SashaBuilder3]

Guys,

Here is the entire SQL I run:

UPDATE t1
SET fld2 =
(SELECT fld2
FROM t2
WHERE t2.fld1=t1.fld1 AND
rownum=1)
WHERE t1.fld1 IN
(SELECT fld1
FROM t2);


Table t1 after this became:


FLD1 FLD2
---------- ----------
a 5
b 2
c 3
d 1
e 7
f 3



meaning it works fine.


Alexandre