x

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

more ▼

asked Feb 24, 2013 at 07:22 PM in Default

avatar image

whitter
40 2 2 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 25, 2013 at 08:33 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x449
x388
x174
x9

asked: Feb 24, 2013 at 07:22 PM

Seen: 938 times

Last Updated: Feb 25, 2013 at 09:36 AM

Copyright 2017 Redgate Software. Privacy Policy