asked
SQL Update Statement to switch heaviest Sku to Line item 1.
Below is the Scenario. I need to switch the Heaviest Sku/QTY to Line Item 1 from its original Line item #. Sample table - SKUDETAIL SKU | WEIGHT UPSTART-9 | 10.0 ALLSTR-11 | 11.0 UNICOR-TW-4 | 8.0 Sample Table - OrderDetail ORDERNO | Line Item | SKU | Quantity 00233495765525 | 1 | UNICOR-TW-4 | 1 00233495765525 | 2 | ALLSTR-11 | 2 00233495765525 | 3 | UPSTART-9 | 3 After executing the SQL STATEMENT, I NEED The orderdetail table to look like this. ORDERNO | Line Item | SKU | Quantity 00233495765525 | 1 | ALLSTR-11 | 2 00233495765525 | 2 | UNICOR-TW-4 | 1 00233495765525 | 3 | UPSTART-9 | 3 A few things to note. Order can have 30+ line items. If two items have the heaviest weight, I would grab the first interation of the two. I know how to switch items using an update. But I am unsure of how swapping the two data elements would be done without losing the data in the other. Unless you use some sort of parameter driven update. Any help would be appreciated. Thanks
If I did understand it correctly, Is not ROW_NUMBER can be used here to find the ORDER needed? For e.g. something like ;WITH CTE AS ( select * --FOR BREVITY , ROW_NUMBER() OVER (PARTITION BY ORDERNO ORDER BY SKUD.WEIGHT, ORDERD.LINEITEMNO) NEWLINENO FROM SKUDETAIL SKUD JOIN ORDERDETAIL ORDERD ON JOININGCOLUMNS ) UPDATE ORDERD SET LINEITEMNO = NEWLINENO FROM SKUDETAIL SKUD JOIN ORDERDETAIL ORDERD ON JOINCOLUMNS Sorry, but right now I do not have SQL server available, so cannot give a precise/full answer :( But hopefully, this will give you the core idea.
12 People are following this question.
