Update with subquery

[noodles1]

I seem to have a problem with a Update using a correlated subquery. When I run the following:

UPDATE Agtable1 t1
SET t1.ztAddr_Line1 = (select t2.Addr_Line1 from Address t2
where t2.Addr_Type = t1.ztAddr_Type AND
t2.Addr_Ident Code1 = t1.ztagree_no )

the subquery is returning 0 rows thus the column in t1 is being set to NULL.

However running a simple select that joins t1 and t2 using the same join condition as the subquery, returns the expect number of rows.

Am I missing something here? Any ideas?

 

[JarlH]

The subquery must return exactly one row (or less) for each row to be updated. Otherwise the UPDATE is supposed to raise an error.

Executing the the subquery on its own makes no sense, because in the UPDATE case the subquery must return not more than one row/value for each row in the update table (Agtabe1).

To find out if the subquery returns too many rows for any of the Agtable1 rows, you can simply do something like:

SELECT t1.*,(SELECT COUNT(*) FROM Address t2
where t2.Addr_Type = t1.ztAddr_Type AND
t2.Addr_Ident Code1 = t1.ztagree_no) AS COUNT_t2
FROM Agtable t1

If COUNT_t2 > 1 for one or more t1 rows, the whole UPDATE will fail.

I suggest you try to find out how to make the subquery safe. When t2 wants to return several rows for a t1 row, which one is supposed to be as update value? Maybe you can use MAX/MIN to pick the wanted row, like:

UPDATE Agtable1 t1
SET t1.ztAddr_Line1 = (select MAX(t2.Addr_Line1) from Address t2
where t2.Addr_Type = t1.ztAddr_Type AND
t2.Addr_Ident Code1 = t1.ztagree_no )

 

[noodles1]

JarlH,

Thanks for the reply.

In my circumstance, there can on be a 1-1 (or 0-1) relationship between rows across the 2 tables according to my join condition. Meaning that for each row in t1 there can only ever be 0 or 1 matching rows from t2 according to my join condition.

Does that alter your thinking?

 

[PHV]

And what about this ?
UPDATE Agtable1 t1
SET t1.ztAddr_Line1 = (select t2.Addr_Line1 from Address t2
where t2.Addr_Type = t1.ztAddr_Type AND
t2.Addr_Ident Code1 = t1.ztagree_no )
WHERE EXISTS (select * from Address t3
where t3.Addr_Type = t1.ztAddr_Type AND
t3.Addr_Ident Code1 = t1.ztagree_no )

Anyway are you sure of this ?
t2.Addr_Ident[highlight] [/highlight]Code1 = t1.ztagree_no

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[noodles1]

PHV,

My typing at fault. That snippet should be t2.Addr_Ident_Code1.

What I do have is 15515 rows in t1, 302510 rows in t2 and 14110 rows that match between t1 & t2 based on the join condition.

I tried your proposed solution but get 0 rows updated.