Updating rows based on rownumber in SQL Server 2012
I was provided with some data in a spreadsheet that will go into automated import soon, so I cannot manually write to the spreadsheet. The data mainly has the following columns. Trayid, trayname, itemdescription and rownumber. I did not compile these tables myself, otherwise I would build them differently, but I have to stick to the format that is already set.
The data that is imported will look like this.
Trayid | Trayname | ItemDescription | RowNumber
1 Tray 1 Product 1 1
Product 2 2
Product 3 3
Product 4 4
2 Tray 2 Product 1 1
Product 2 2
Product 3 3
Product 4 4
Product 5 5
What I need to do is update the tray and trayname for each of the other lines after line 1, so for example this will look like.
Trayid | Trayname | ItemDescription | RowNumber
1 Tray 1 Product 1 1
1 Tray 1 Product 2 2
1 Tray 1 Product 3 3
1 Tray 1 Product 4 4
2 Tray 2 Product 1 1
2 Tray 2 Product 2 2
2 Tray 2 Product 3 3
2 Tray 2 Product 4 4
2 Tray 2 Product 5 5
I guess I need to use a cursor or something, but I'm not sure, I think it can be done by dropping the rowan trees and stopping when it sees ripples 1 again, and then continue with the next trader and trainee.
Sorry if what I need doesn't make sense, it was awkward to explain.
source to share
SQL tables have no inline ordering. Therefore, you cannot depend on it. But there is something you can do:
- Define a column
identity
in the source table. - Create a view on the original table that excludes personality.
- Bulk insert into view.
This will assign a sequence number to the rows in the same order as the original data. Let's call it id
. Then you can make your update by following these steps:
with toupdate (
select t.*,
max(TrayId) over (partition by grp) as new_TrayId,
max(TrayName) over (partition by grp) as new_TrayName
from (select t.*,
count(TrayId) over (order by id) as grp
from t
) t
)
update toupdate
set TrayId = new_TrayId,
TrayName = new_TrayName
where TrayId is null;
The idea is to define line groups corresponding to each tray. The simple idea is to count the number of non-NULL values to any given string - everything in the group will have the same value grp
. The window functions then distribute the actual value across all rows in the group (using max()
), and these values are used to update.
source to share