update subselect

[gdg1976]

hi all,

input
[pre]TAB1
COL1 COL2 COL3 COL4
AAA 1 A1 1
AAA 2 A2 1
AAA 3 A3 0
BBB 9 A1 1
BBB 99 A2 1
BBB 999 A3 0[/pre]

How can i obtain the follow output with an update ?


output
[pre]TAB1
COL1 COL2 COL3 COL4
AAA 9 A1 1
AAA 99 A2 1
AAA 3 A3 0
BBB 9 A1 1
BBB 99 A2 1
BBB 999 A3 0[/pre]


Is this update enought ?

update tab1 a set a.col2 =
(select b.col2 from tab1 b
where b.col3 = a.col3
and b.col1 = 'BBB')
where a.col4 = 1
and a.col1 = 'AAA'

thanks to all in advance.
bye