Update data with same values

Question

Update data with same values

Chuck S

Old Hand

Points: 302
More actions
September 5, 2017 at 9:34 am

#339685

Hello,
I have a table with the header and child record in the same table. I need to be able to mark all the parent records.
This is what my table looks like.

I need it to look like this.
I have a the script for table and data in file.
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

Do you have any kind of key? How do we determine what rows are linked to which in your data, as it currently appears to be unordered.

Once we have a way to order your data, this will be much easier to provide an answer for.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Chuck S Old Hand Points: 302 More actions · Answer 2

Thom A - Tuesday, September 5, 2017 9:44 AM
Do you have any kind of key? How do we determine what rows are linked to which in your data, as it currently appears to be unordered.
Once we have a way to order your data, this will be much easier to provide an answer for.

Hi Thom,

Unfortunately I don't have any kind of key. It's fixed order so the rows below the Order_ID coincide with the order.

Thanks

sgmunson SSC Guru Points: 110639 More actions · Answer 3

Chuck S - Tuesday, September 5, 2017 10:50 AM
Thom A - Tuesday, September 5, 2017 9:44 AM
Do you have any kind of key? How do we determine what rows are linked to which in your data, as it currently appears to be unordered.
Once we have a way to order your data, this will be much easier to provide an answer for.
Hi Thom,
Unfortunately I don't have any kind of key. It's fixed order so the rows below the Order_ID coincide with the order.
Thanks

Unfortunately, that's not going to help. SQL Server does not guarantee that records appear in a given order without an ORDER BY clause, so if the query that produced the data you've displayed doesn't have one, then there's no way to be sure you can accomplish your goal.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

sgmunson - Tuesday, September 5, 2017 10:58 AM
Chuck S - Tuesday, September 5, 2017 10:50 AM
Thom A - Tuesday, September 5, 2017 9:44 AM
Do you have any kind of key? How do we determine what rows are linked to which in your data, as it currently appears to be unordered.
Once we have a way to order your data, this will be much easier to provide an answer for.
Hi Thom,
Unfortunately I don't have any kind of key. It's fixed order so the rows below the Order_ID coincide with the order.
Thanks
Unfortunately, that's not going to help. SQL Server does not guarantee that records appear in a given order without an ORDER BY clause, so if the query that produced the data you've displayed doesn't have one, then there's no way to be sure you can accomplish your goal.

<rant>

Another thing to note is that this design is not normalized and will be a big pain to query efficiently. Do yourself a favor and think on changing it to a proper 3NF design.
</rant>

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76945 More actions · Answer 5

I would just tell the person who is delivering this data file that it's in an unacceptable format and have them fix it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Chuck S Old Hand Points: 302 More actions · Answer 7

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).

Hi Thanks I'll give it a try. 🙂

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

Chuck S - Tuesday, September 5, 2017 9:34 AM
Hello,
I have a table with the header and child record in the same table. I need to be able to mark all the parent records.
This is what my table looks like.

I need it to look like this.
I have a the script for table and data in file.
Thanks

Does the data actually originate from a file? I ask because, if it does, we might be able to do this.

{EDIT} Sorry... didn't see Kevin's post, which is spot on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

If the OP knows SSIS that can definitely be used to track the Order_ID and insert rows with the same value until the New_Order_ID <> Prev_Order_ID. Pretty straight-forward scripting to get that to work.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Chuck S Old Hand Points: 302 More actions · Answer 10

TheSQLGuru - Tuesday, September 5, 2017 11:50 AM
If the OP knows SSIS that can definitely be used to track the Order_ID and insert rows with the same value until the New_Order_ID <> Prev_Order_ID. Pretty straight-forward scripting to get that to work.

Hi Kevin,

What is OP?

drew.allen SSC Guru Points: 76945 More actions · Answer 11

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).

Actually, given such an identity, there is a much better approach.

/* Create sample data */ CREATE TABLE #Orders( ID INT IDENTITY NOT NULL, Order_ID CHAR(20) )

INSERT #Orders(Order_ID) VALUES('10000'), (''), (''), ('9999'), (''), ('') ;

/* Solution */ WITH CTE AS ( SELECT ID, Order_ID, CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(NULLIF(Order_ID, '') AS BINARY(20))) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING), 6, 20) AS CHAR(20)) AS mx FROM #Orders ) UPDATE CTE SET Order_ID = mx

/* Cleanup */ SELECT * FROM #Orders

DROP TABLE #Orders

You can convert to (N)(VAR)CHAR instead of binary, but you would have to make sure that you have the order correct (that is, you need to left pad the identity column with either 0 or space).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76945 More actions · Answer 12

Chuck S - Tuesday, September 5, 2017 11:56 AM
TheSQLGuru - Tuesday, September 5, 2017 11:50 AM
If the OP knows SSIS that can definitely be used to track the Order_ID and insert rows with the same value until the New_Order_ID <> Prev_Order_ID. Pretty straight-forward scripting to get that to work.
Hi Kevin,
What is OP?

OP = Original Poster

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Chuck S Old Hand Points: 302 More actions · Answer 13

drew.allen - Tuesday, September 5, 2017 11: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).
Actually, given such an identity, there is a much better approach.
/* Create sample data */ CREATE TABLE #Orders( ID INT IDENTITY NOT NULL, Order_ID CHAR(20) )
INSERT #Orders(Order_ID) VALUES('10000'), (''), (''), ('9999'), (''), ('') ;
/* Solution */ WITH CTE AS ( SELECT ID, Order_ID, CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(NULLIF(Order_ID, '') AS BINARY(20))) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING), 6, 20) AS CHAR(20)) AS mx FROM #Orders ) UPDATE CTE SET Order_ID = mx
/* Cleanup */ SELECT * FROM #Orders
DROP TABLE #Orders
You can convert to (N)(VAR)CHAR instead of binary, but you would have to make sure that you have the order correct (that is, you need to left pad the identity column with either 0 or space).
Drew

Thanks Drew!

Chuck S Old Hand Points: 302 More actions · Answer 14

drew.allen - Tuesday, September 5, 2017 11: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).
Actually, given such an identity, there is a much better approach.
/* Create sample data */ CREATE TABLE #Orders( ID INT IDENTITY NOT NULL, Order_ID CHAR(20) )
INSERT #Orders(Order_ID) VALUES('10000'), (''), (''), ('9999'), (''), ('') ;
/* Solution */ WITH CTE AS ( SELECT ID, Order_ID, CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(NULLIF(Order_ID, '') AS BINARY(20))) OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING), 6, 20) AS CHAR(20)) AS mx FROM #Orders ) UPDATE CTE SET Order_ID = mx
/* Cleanup */ SELECT * FROM #Orders
DROP TABLE #Orders
You can convert to (N)(VAR)CHAR instead of binary, but you would have to make sure that you have the order correct (that is, you need to left pad the identity column with either 0 or space).
Drew

Hi Drew,
That worked like a charm. Thanks 🙂