write an update query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi everyone,

My select query returns following dataset

HTML

ID      Col2    selected
------------------------
13	449	0
13	3804	0
13	3805	0
13	3806	0
230	274	0
230	1589	0
650	2375	0
911	1147	0
963	1157	0
1010	1196	0
1010    2000    0
1130	1410	0
1130	1456	0
1130	1910	0

Now I want to write an update query which updates first row of each ID. So the desire result is like this.

HTML

ID      Col2    selected
------------------------
13	449	1
13	3804	0
13	3805	0
13	3806	0
230	274	1
230	1589	0
650	2375	1
911	1147	1
963	1157	1
1010	1196	1
1010    2000    0
1130	1410	1
1130	1456	0
1130	1910	0

Thanks in Advance..... :-)

Posted 2-Sep-11 11:27am

hiren soni

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bun Leap_kh · Answer 1 · 2011-09-02T19:02:00

Or you can try with Cursor.

declare @id, @tmp_id, @col2 int
declare cr cursor local scroll
for select ID, col2 from MyTable order by ID
open cr
fetch next from cr into @id, @col2
while @@fetch_status=0
begin
if @id<>@tmp_id
begin
update MyTable set selected=1 where ID=@id and col2=@col2
set @tmp_id=@id
end
fetch next from cr into @id
end
close cr
deallocate cr

Wendelius · Answer 2 · 2011-09-02T11:36:00

Solution 1

So are you trying to update the rows with the lowest Col2 for each Id? If that'ts the case, you could try something like:

SQL

UPDATE MyTable
SET Selected = 1
WHERE (id, Col2) IN (SELECT mt2.Id, MIN(mt2.Col2)
                     FROM MyTable mt2
                     GROUP BY mt2.Id)

or

SQL

UPDATE MyTable
SET Selected = 1
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable mt2
                  WHERE mt2.Id = MyTable.Id
                  AND   mt2.Col2 < MyTable.Col2)

Posted 2-Sep-11 11:36am

Wendelius

Comments

hiren soni 2-Sep-11 17:45pm

Hi Mika,

Thanks for your quick reply, I tried your first query but it gives me this error. Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Wendelius 2-Sep-11 17:56pm

I can't remember if SQL 2005 has the ability to use several columns in the IN clause... Did you try the other one?

Simon Bang Terkildsen 2-Sep-11 18:07pm

SQL Server no matter which one does not support multi column IN. I haven't read up on SQL Server "Denali" MS might add support for it in that version, but I highly doubt it.
The other SQL one on the other hand should work beautifully. +5

Wendelius 2-Sep-11 18:14pm

Thanks for the vote. Currently I only have Oracle installed so I'm not able to check this on Denali or previous versions. I remember that the older versions don't support this, but it's a shame if it hasn't been added... :)

Simon Bang Terkildsen 2-Sep-11 18:23pm

I've just glanced through the what's new in SQL Server Denali, and from what I can see there is no mention of IN unfortunately, seems most of the T-SQL enhancements are about formatting nothing exciting there :)

Wendelius 2-Sep-11 18:52pm

Just tested this on Denali and it's not supported :( So as you said, nothing new...

write an update query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

write an update query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us