Menu
I have a table like this
CREATE TABLE [dbo].[tblUserLink](
[IDUserLink] [int] IDENTITY(1,1) NOT NULL,
[IDUser] [int] NOT NULL,
[IDRegie] [varchar](50) NOT NULL,
[DefaultAgency] [bit] NULL
)
I'm looking for a query /script to update the field DefaultAgency. I'd like to set it to 1 for the first record that comes for each group of record with a same IDUser.
example :
IDUserLink IDUser IDRegie DefaultAgency (Goal)
1 1 1 null DefaultAgency to be set to 1
2 1 2 null
3 1 3 null
4 2 2 null DefaultAgency to be set to 1
5 2 1 null
6 3 1 null DefaultAgency to be set to 1
...etc
Can I achieve this using a simple sql query or should I script it ?
401
First, you need to write a CTE in which you assign a number to each row within each group. To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
Typically, these are accomplished using the TOP or LIMIT clause. Problem is, Top N result sets are limited to the highest values in the table, without any grouping. The GROUP BY clause can help with that, but it is limited to the single top result for each group.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
update tblUserLink
set DefaultAgency = 1
where IDUserLink in
(select min(IDUserLink) from tblUserLink group by IDUser)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
