Get back record mistakenly updated due to subquery wrong alias name

Question

I was about to execute below update query -

update t1 set t1.a=(select distinct t2.a from t2 where t1.id=t2.id and rownumber=1) WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id= t2.id and  T1.email='[email protected]' )

But, I have mistakenly executed below query with subquery also including t1. Now, I am affraid that records has been updated wrong.

update t1 set t1.a=(select distinct t2.a from t2,t1 where t1.id=t2.id and rownumber=1) WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id= t2.id and T1.email='[email protected]' )

Now I am trying to find out records which got updated due to wrong update query.

Any help will be highly appreciated.

Thanks.

Answer 1

Basically, you should assume that all rows were updated with the wrong value and just re-run the update:

update t1
    set t1.a = (select t2.a
                from t2
                where t1.id = t2.id and t2.rownumber = 1
               );

You didn't change the value of id or rownumber, so I think this will be fine.

Note that the select distinct is useless in the subquery. Presumably, rownumber = 1 is only selecting one row (if not, you are using a very poor naming convention).