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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 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...

Bun Leap_kh · Answer 2 · 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