Unanswered: What am I doing wrong in this Update statement
Wonder what I'm doing wrong in this update statement.
The subquery returns the correct no of rows (4085) whereas the whole query when updated returns 3-3.5 times more rows.
update proddta.f1212 set main = 'FDX'
(select ammin from proddta.f1212
join proddta.f7055 on amdb=cndb
join proddta.f9011 a on a.r6tmt=cntmt
join proddta.f9011 b on b.r6tmt=amtmt
where amtmt < 4500 and a.r6cnt = 'US' and ammin <> 'DHL' and amdb in ('1111', '2222'));
if "3-3.5 times more rows" are all rows in F1212, then the behaviour is correct.
As the subquery returns some rows, it is TRUE for all updated rows.
If you want to take (correlate the subquery with) values from the updated table, just remove reference to F1212 from the FROM clause of the subquery.
You may find some examples with sample codes e.g. in this thread: http://www.dbforums.com/oracle/16299...mn-update.html
(especially examine the last post)