-
Update Statement
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?
-
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
-
Thanx for the solution.
In the same table how can I get ID's with Field1+Field2 is unique and count(ID)>1?
-
select max(ID) as ID,Field1,Field2 as mycolumn from myTable group by Field1,Field2 having count(ID)>1
-
select * from mytable where field1+field2 in (
select Field1+Field2 as mycolumn from myTable group by Field1,Field2 having count(ID)>1 )
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|