Help on Update Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help on Update Statement

Author

Topic

faimuj
Starting Member

9 Posts

Posted - 2012-03-31 : 16:54:25

Hello,

I needed some help on how to set records that where cancelled. Here is the table (Orders) and the relevant fields:

Orders

Order_ID (VARCHAR(25))
Cancelled (BIT)

Using the Order_ID, what I need to do is to find any orders that were cancelled and its corresponding original order(s). The Order_ID can be of variable length, usually from 15-20 characters. The first left(Order_ID, len(Order_ID)-2) characters of the Order_ID will always be the same for an order. Although, the last two characters determine if the order was cancelled. For example, if there is an order that was not cancelled, it would end with a '00' and there would only be one record. If the order is cancelled, the Order_ID would end with an odd number, like '01', and there would be at least two records: the original record and the cancelled record. For the cancelled orders, there could be cases where there are record count > 2 for same order. For example, an order could be cancelled, re-billed and then cancelled again. So, in this case, there would be the original order (ending with '00'), the cancelled order (ending with '01'), the re-billed order (ending with '02'), and the cancelled order (ending with '03'). In total you have four records for the one order. Theoretically, you can have x number of records for an order.

What I have to do is identify the orders that were cancelled and set the Cancelled flag to '1' where the order was cancelled. If the order was cancelled and then re-billed, I have only have to set the Cancelled = '1' for the records that were cancelled and not re-billed. Sample data is worth a thousand words:

Order_ID
9876543210Y456700
1234567890X123400
1234567890X123401
7654321078Z12345600
7654321078Z12345601
7654321078Z12345602
7654321078Z12345603
2135467826A898779100
2135467826A898779101
2135467826A898779102
2135467826A898779103
2135467826A898779104

The Order_IDs that should be set to Cancelled (Cancelled = '1') are:
1234567890X123400
1234567890X123401
7654321078Z12345600
7654321078Z12345601
7654321078Z12345602
7654321078Z12345603
2135467826A898779100
2135467826A898779101
2135467826A898779102
2135467826A898779103

Notice that the first (9876543210Y456700) and last (2135467826A898779104) Order_IDs were not set. The first as it was not cancelled. The last order had five records, all of them are set to cancelled except the Order_ID ending with '04' as it was not cancelled. Here should be the final results:
Order_ID Cancelled
9876543210Y456700 0
1234567890X123400 1
1234567890X123401 1
7654321078Z12345600 1
7654321078Z12345601 1
7654321078Z12345602 1
7654321078Z12345603 1
2135467826A898779100 1
2135467826A898779101 1
2135467826A898779102 1
2135467826A898779103 1
2135467826A898779104 0

Thank you in advance for you assistance.

quote:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 19:26:04

why are you maintaining the status like this? why change Order_ID itself to indicate the status? Why not hold a separate bit for indicating status? if you maintain above way you wont have striaght forward way of identifying single order records

anyways, if you've to live with this mess, the below should be update


;With Temp
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY left(Order_ID, len(Order_ID)-2) ORDER BY RIGHT(Order_ID,2)*1 DESC) AS Rn,* 
FROM table
)
UPDATE t
SET t.Cancelled=1
FROM (SELECT *,MAX(CASE WHEN Rn=1 THEN RIGHT(Order_ID,2)*1 END) OVER (PARTITION BY left(Order_ID, len(Order_ID)-2)) AS MaxValue FROM Temp)t
WHERE MaxValue >0
AND RIGHT(Order_ID,2)*1 <> MaxValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

faimuj
Starting Member

9 Posts

Posted - 2012-04-02 : 15:08:18

Hello visakh16,

Thank you for your response. Unfortunately, I have to live with the mess. I did try to run your statement and it threw an error stating it had an issue with the Update statement. It did not provide much more information on it. Although, I did make a few minor changes to convert the right digits from varchar to int:

;With Temp
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY left(Order_ID, len(Order_ID)-2) ORDER BY RIGHT(Order_ID,2)*1 DESC) AS Rn, *
FROM Orders
)
UPDATE t
SET t.Cancelled = 1
FROM (SELECT *,MAX(CASE WHEN Rn = 1 THEN convert(int,right(Order_ID,2))*1 END) OVER (PARTITION BY left(Order_ID, len(Order_ID)-2)) AS MaxValue FROM Temp) t
WHERE MaxValue > 0
AND convert(int,right(Order_ID,2))*1 <> MaxValue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-02 : 15:45:11

whats the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

faimuj
Starting Member

9 Posts

Posted - 2012-04-02 : 18:44:57

I get:

Could not execute statement.
Syntax error near 'UPDATE' on line 5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources