Updating with correlated query

[cak]

Moving from Oracle to SQL/Server and have a question on the correct way to do an update using a correlated subquery.

Here is my Oracle query .. I'm updating two fields at once. Just a note all data resides in the same table.

update account a
set (region, x_dtr_cd) = (select distinct region, x_dtr_cd
from account r where r.if_row_batch_num = 45300
and a.accnt_num = r.accnt_num)
where a.if_row_batch_num between '30100' and '30150' ;
In SQL/Server I can update each individually as follows:

update account
set region = (select distinct region
from account r
where r.if_row_batch_num = 45300
and account.accnt_num = r.accnt_num)
where if_row_batch_num between '30100' and '30150'

update account
set x_dtr_cd = (select distinct x_dtr_cd
from account r
where r.if_row_batch_num = 45300
and account.accnt_num = r.accnt_num)
where if_row_batch_num between '30100' and '30150'

Is there a way to do this update with one SQL ?? I tried several versions, but have had no luck getting it to work.

Any thoughts ??

Thanks.

[nosepicker]

Try this:

update account
set region = A.region, x_dtr_cd = A.x_dtr_cd
from
(select distinct r.accnt_num, r.region, r.x_dtr_cd
from account r
where r.if_row_batch_num = 45300) AS A
where account.accnt_num = A.accnt_num
and account.if_row_batch_num between '30100' and '30150'

[cak]

Thanks, that is exactly what I needed !! I just have to think a little differently in SQL/Server vs. Oracle !!!