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
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.
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
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.
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
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.
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)
iam using dbforge of 6.3.341 in this which sql version will present
18 People are following this question.
