Update each 1st row with the same value in a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update each 1st row with the same value in a field

Author

Topic

sundawn
Starting Member

5 Posts

Posted - 2008-10-24 : 09:17:09

Hi,

i have a Table with the columns "id, default, customer" (and some others).
Now i want to update "default" = 1 but only 1 row out of all with the same value in "customer".

For example the final table should look like this:

ID | DEFAULT | CUSTOMER
1 | 1 | 123
2 | NULL | 123
3 | NULL | 123
4 | 1 | 355
5 | NULL | 355
6 | 1 | 666
7 | 1 | 777

But i have no idea how to solve this in one query.

Hope you can help me with this,
thanks Sundawn

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-24 : 09:24:46

I'm not sure I understand your question but this will update a single value in the Default column for a specified customer where Default=1.

Update TableName
set Default= newValue
where ID in (
Select top 1 ID from
TableName where Customer= customerValue and default =1)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-24 : 09:29:58

[code]UPDATE a
SET Default = 1
FROM tablename AS a
INNER JOIN (SELECT Customer, ID = MIN(ID) FROM tablename GROUP BY Customer) AS b
ON a.ID = b.ID[/code]

- Lumbago

sundawn
Starting Member

5 Posts

Posted - 2008-10-24 : 09:30:13

sundawn
Starting Member

5 Posts

Posted - 2008-10-24 : 09:31:31

UPDATE a
SET Default = 1
FROM tablename AS a
INNER JOIN (SELECT Customer, ID = MIN(ID) FROM tablename GROUP BY Customer) AS b
ON a.ID = b.ID

Hmm this could work - thx i will test this :)

sundawn
Starting Member

5 Posts

Posted - 2008-10-24 : 10:19:18

Ok the final solution i used:

update tablename
set default = 1
where id in ( select min(id) from tablename group by customer)

thx to all for help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources