Update data with same values

Question

Update data with same values

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

TheSQLGuru - Tuesday, September 5, 2017 11:21 AM
Depending on how much data you have this shouldn't be too bad, although as others say it is not a good design. But maybe from this you will take it to such.
Create an int (assuming you have < ~4.2B rows of data) identity and insert into that in a manner that DOES preserve order (both SSIS and BCP and probably others can do this). Now you DO have a way to link a row to it's predecesor, and there are a variety of ways to "fill in" the missing Order_IDs. Simplest is probably using LAG 1 and update blank rows with lag 1 row where order_id is empty and lag 1 is not empty. Iterative and ugly, but it will work. I'm sure there is a spiffier set-based way but don't know at this point if it is necessary (and sadly I don't have time to fashion/test it in any case).

...Now you DO have a way to link a row to it's predecessor...

The OP needs to be aware that any failed attempted inserts will leave gaps in the IDENTITY sequence and then above will not be possible.

Far away is close at hand in the images of elsewhere.
Anon.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

David Burrows - Wednesday, September 6, 2017 1:59 AM
TheSQLGuru - Tuesday, September 5, 2017 11:21 AM
Depending on how much data you have this shouldn't be too bad, although as others say it is not a good design. But maybe from this you will take it to such.
Create an int (assuming you have < ~4.2B rows of data) identity and insert into that in a manner that DOES preserve order (both SSIS and BCP and probably others can do this). Now you DO have a way to link a row to it's predecesor, and there are a variety of ways to "fill in" the missing Order_IDs. Simplest is probably using LAG 1 and update blank rows with lag 1 row where order_id is empty and lag 1 is not empty. Iterative and ugly, but it will work. I'm sure there is a spiffier set-based way but don't know at this point if it is necessary (and sadly I don't have time to fashion/test it in any case).
...Now you DO have a way to link a row to it's predecessor...

The OP needs to be aware that any failed attempted inserts will leave gaps in the IDENTITY sequence and then above will not be possible.

The LAG method is not affected by gaps. Neither is the windowed MAX method. Both depend only on the rows being in the correct order.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA