Update Statement

[Manjunath]

I am looking for a solution to my unique problem.

myTable is having following records

ID Field1 Field2 Field3 Field4
1 x y 100 0
2 xx zz 5 0
3 x y 200 1
4 a b 1 1
5 a b 2 0
6 k L 78 1
7 aa bb 25 1
8 k L 15 0

Now the problem is if I want to update a latest record's(if you go by ID)Field4 with 2 for the records where Field1 + Filed2 is unique, can I do this in a single query?

I can select the unique record by the following query

select Field1,Field2 from myTable group by Field1,Field2 having count(ID)>1

Any ideas?

[MAK]

use tempdb
go
create table mytable (ID int,Field1 varchar(10),Field2 varchar(10),Field3 int, Field4 int)
go
insert into mytable select 1, 'x', 'y', 100, 0
insert into mytable select 2, 'xx', 'zz' ,5, 0
insert into mytable select 3, 'x', 'y', 200, 1
insert into mytable select 4, 'a', 'b', 1, 1
insert into mytable select 5, 'a', 'b', 2, 0
insert into mytable select 6, 'k', 'L' ,78, 1
insert into mytable select 7,'aa', 'bb' ,25, 1
insert into mytable select 8, 'k', 'L' ,15, 0

go
select * from mytable
go

update a set field4=2 from mytable a, (select max(ID) as ID,Field1+Field2 as mycolumn from myTable group by Field1,Field2 having count(ID)>1 ) as b
where a.id = b.id
go

[Manjunath]

Thanx for the solution.

In the same table how can I get ID's with Field1+Field2 is unique and count(ID)>1?

[MAK]

select max(ID) as ID,Field1,Field2 as mycolumn from myTable group by Field1,Field2 having count(ID)>1

[MAK]

select * from mytable where field1+field2 in (
select Field1+Field2 as mycolumn from myTable group by Field1,Field2 having count(ID)>1 )