Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2, it updates every single
record in the lta table, what's going on here? any thoughts?

1.) select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe

2.)
update lta
set lta.LL_RCVD = 'N'
where (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)

My bad, query #2 is missing the "exists" phrase, it should read:

update lta
set lta.LL_RCVD = 'N'
where exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)

Roy:

Since your subquery returns 357 records the exists always returns true.
Thus, you are saying Update where true. Since true is always true you
update all records. Just try using a join in your update as below:

UPDATE T1 SET
LL_RCVD = 'N'
FROM
LTA T1 INNER JOIN NEW_LIST T2 ON
T1.VOY = T2.VOY AND
T1.POE = T2.POE

Scott



"Roy" <roy.anderson (AT) gmail (DOT) com> wrote

Your syntax is not exactly right for the exists clause, try this
instead:

update lta
set lta.LL_RCVD = 'N'
where exists (select 1 from new_list where lta.voy = new_list.voy AND
lta.poe = new_list.poe)

This will work in t-sql, but is not standard sql syntax:
UPDATE T1 SET
LL_RCVD = 'N'
FROM
LTA T1 INNER JOIN NEW_LIST T2 ON
T1.VOY = T2.VOY AND
T1.POE = T2.POE