Insert/Update and Delete Change

hi,

I have a table which contained 5 columns and with 2 primary keys

Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |

ab | 1 | abc | null | null

ab | 2 | def | null | null

Col 1 and Col 2 both are primary keys.

How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?

Thanks

SQL is a set-based language so you can do below in an UPDATE statement to swap the column values:

update tbl

set Col2 = case Col2 when 1 then 2 when 2 then 1 end

where Col2 in (1, 2)

Logically with DML statements all the rows are affected at once so the constraint violation will not happen in your particular case. The technique that allows such changes to happen is called Halloween Protection. This is done by using table spools or split operators. If you look at the showplan of the above update statement, you can see it in action.

|||

update table1
set col2=case col2 when 1 then 2 else 1 end

You should pay attention to the order by the data.

/*

col1 col2 col3 col4 col5
- -- - - -
ab 1 def NULL NULL
ab 2 abc NULL NULL

*/

First, I was cheated by the order.