By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Mysterious query problem?

P: n/a
Roy
Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, 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 exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Roy
Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!

update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe

Jul 23 '05 #2

P: n/a
On 10 Jan 2005 07:25:14 -0800, Roy wrote:
Hey all,
strange problem here... query #1 displays 357 records correctly and all
is well. However, when placed within query #2 as a subquery, 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 exists (select *
from LTA INNER JOIN new_list
ON lta.voy = new_list.voy AND
lta.poe = new_list.poe)


Hi Roy,

The subquery on #2 doesn't reference the outer query. Hence, it returns
the same 357 rows for each row in LTA (from the outer query), so the
EXISTS predicate is always true.

You probably need

UPDATE lta
SET LL_RCVD = 'N'
WHERE EXISTS (SELECT *
FROM new_list
WHERE new_list.voy = lta.voy
AND new_list.poe = lta.poe)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3

P: n/a
On 10 Jan 2005 07:42:14 -0800, Roy wrote:
Gah! What am I doing wrong? The query below is a modification of query
#2 above, yet updates 32 records (all of which are NOT located in table
new_list)??!
Please help!

update lta
set LL_RCVD = 'j'
from lta a, new_list b
where a.voy = b.voy AND
a.poe = b.poe


Hi Roy,

This should affect the same rows as the query I suggested in my reply to
your other message (but this one sets LL_RVCD to 'j'; the other one sets
it to 'N').

An important difference occurs if one row in lta matches more than one row
in new_list (and, since the select returns more rows than are affected by
the update statement, this appears to be the case with your data). The
above query will repeatedly change the values for any lta row that matches
more than one new_list row (with the results being undefined, though in
you case, where the new value is a constant, the results will be as
expected); my version will simply update the rows exactly once.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

P: n/a
Roy
I love you forever, thanks Hugo!

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.