SET COL1 = (SELECT TAB2.COL1
WHERE TAB2.COL2 = 'XX');
In the code above, I would like to know the kind of locks applied on the tables TAB1 and TAB2.
And... I would also like to know what would be the effect on the update statement on specifying the clause- "WITH UR"
in the update statement as ...
SET COL1 = ( SELECT TAB2.COL1
WHERE TAB2.COL2 = 'XX')
I am answering this from the top of my head. Generally locks are defined when they define a Table space.
So for your first question, it depends upon the TS the Tables are present in.
For the 2nd question, Gurmeet is right, u will get an SQL Code, u cannot update a table with UR.