question

sunil_1234 avatar image
sunil_1234 asked

i had an table with thousands of record given below i want to update ignore_row=1 where TranType=undo and the before row of undo

Barcode TranType ignore_row TranDate HC5590 OrderOut (null) 11/15/2015 08:32:03.0 NN9927 OrderOut (null) 11/15/2015 05:35:38.0 CM2618 OrderOut (null) 11/15/2015 05:35:32.0 NN9927 undo (null) 11/15/2015 05:35:18.0 CM2618 OrderIn (null) 11/15/2015 05:35:11.0 JK4149 OrderIn (null) 11/15/2015 05:34:46.0 JK4149 undo (null) 11/15/2015 05:28:07.0 MR4818 OrderOut (null) 11/15/2015 05:28:04.0 BD0459 OrderOut (null) 11/15/2015 03:30:46.0 C1797 OrderOut (null) 11/15/2015 03:27:44.0 KD3387 undo (null) 11/15/2015 02:33:18.0 HA9113 OrderIn (null) 11/15/2015 02:30:21.0 TJ9858 OrderIn (null) 11/15/2015 01:24:52.0 JK4136 OrderOut (null) 11/15/2015 01:19:17.0 KM1844 OrderOut (null) 11/15/2015 01:10:08.0
update
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
But when you say "preceding row", is that based on the sort order of TranDate or some other sort order? A result set from a query is never sorted unless there's an ORDER BY clause specifying in which order the rows should appear. If ORDER BY is ommitted, the order in which the rows are returned from the database engine is unpredictable. I'll post a solution based on the assumption that rows should be ordered by TranDate.
2 Likes 2 ·
Gazz avatar image Gazz commented ·
How do you know these rows will always appear in the same order?
0 Likes 0 ·
sunil_1234 avatar image sunil_1234 commented ·
its an raw data where i should update he ignore_row=1 of trantype=undo and the before row. can we use the date row and update it
0 Likes 0 ·
Gazz avatar image Gazz commented ·
Sorry, I cant help you - There are two ways I know how to do what you are asking for, but you need to have the table in a sorted order for both to work.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
It looks like on your output like the column to order by is the TranDate. Now, just so I understand what it is you want to do: For all rows having TranType=Undo: - Update the preceding row (based on ascending sort order for TranDate) with the value 1 in ignore_row column. Should that just be the preceding row, based on the TranDate, or should it be a row with the same BarCode value as the row with TranType=undo? - Update the row with TranType=undo with the value 1 in the ignore_row column. If that's correctly understood, I would have a solution for you, but please confirm or correct my assumption first.
0 Likes 0 ·
Show more comments

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Based on the assumption that the logical sort order to find "preceding row", I think below code would work. I haven't tested it. There are probably more efficient ways to solve this - I have just written a suggestion from the top of my head. WITH CTE1 AS( SELECT *, ROW_NUMBER() OVER(ORDER BY TranDate) as rownum ), CTE2 AS( SELECT CTE1.* WHERE TranType=Undo ), CTE3 AS( SELECT CTE1.* FROM CTE2 INNER JOIN CTE1 ON CTE2.rownum -1 = CTE1.rownum UNION ALL SELECT * FROM CTE2 )UPDATE CTE3 SET ignore_row=1
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
I have used common table expressions and named them CTE1, CTE2 and CTE3. The queries against the base table use the column names provided in your questions. Since you didn't state which version of SQL Server you are using, I was assuming that you are on SQL Server 2005 or later, and then the syntax in my answer is correct.
1 Like 1 ·
sunil_1234 avatar image sunil_1234 commented ·
can you be more clear in a correct syntax way
0 Likes 0 ·
sunil_1234 avatar image sunil_1234 commented ·
iam using dbforge tool for sql,iam unable to understand the what is cte. write according to the coloumn name and table name(check_copy)
0 Likes 0 ·
sunil_1234 avatar image sunil_1234 commented ·
iam using dbforge of 6.3.341 in this which sql version will present
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.