Posted by Plamen Ratchev on 01/31/08 17:41

If I understand correctly that the requirement is to update the line item
for be a consecutive number based on invoice and sequence number for line
items, then something like this will do (SQL Server 2005):

CREATE TABLE Invoices (
invoiceid INT NOT NULL,
sequencenum INT NOT NULL,
lineitemid INT NOT NULL UNIQUE,
PRIMARY KEY (invoiceid, sequencenum));

INSERT INTO Invoices VALUES (9999999, 1, 11111111);
INSERT INTO Invoices VALUES (9999999, 2, 11111181);
INSERT INTO Invoices VALUES (9999999, 3, 11111191);
INSERT INTO Invoices VALUES (9999999, 4, 11111171);
INSERT INTO Invoices VALUES (9999998, 1, 11111161);
INSERT INTO Invoices VALUES (9999998, 2, 11111121);
INSERT INTO Invoices VALUES (9999998, 3, 11111131);
INSERT INTO Invoices VALUES (9999997, 1, 11111141);
INSERT INTO Invoices VALUES (9999997, 2, 11111101);
INSERT INTO Invoices VALUES (9999997, 3, 11111151);

WITH InvoicesCTE
AS
( SELECT lineitemid,
ROW_NUMBER() OVER(
ORDER BY invoiceid, sequencenum)
AS line_nbr
FROM Invoices)
UPDATE InvoicesCTE
SET lineitemid = line_nbr;

SELECT invoiceid, sequencenum, lineitemid
FROM Invoices;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: Transfer data to web page fields
• Prev in forum: Re: Create Table with Variable included in Table Name
• Thread view: Re: help with update

[Reply to this message]