Prev: Join 4 tables - can it be done?
Next: Transfer data to web page fields
From: Analizer1 on 1 Feb 2008 12:00
Hi all.....Im ok with sql but by no means a guru

i have like 100,000 rows of lineitems
example:
invoiceid, lineitemId, squencenum
9999999 11111111 1
9999999 11111181 2
9999999 11111182 3

the Data consistes of Many Invoices and the Lineitem id
is a Unique Id (Most are consequtive but not a Guarantee) and SequenceNum
is 1,2,3, etc of the line items on the invoice

In my Case i have say 100,000 rows , different Invoice numbers with
different number of lineitems
All the LineItems are out of Sync.....

So I need to Update them in the order 1,2,3 etc for there prospective
Invoice number

Help with a Update Scripts would be helpful
Thanks


From: Plamen Ratchev on 1 Feb 2008 12: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

From: --CELKO-- on 1 Feb 2008 18:37
What you posted makes no sense in an RDBMS.

1) RTables have no ordering; that is a property of a file.

2) ".. SequenceNum is 1,2,3, etc of the line items on the invoice <<

Unh?? Display and formatting is done in the front end and not in the
database. You are still thinking of a COBOL program where display and
data were mixed together in a procedural program, working on one
record at a time

3) "I have say 100,000 rows, different Invoice numbers with different
number of lineitems. All the LineItems are out of synch .. <<

Since a table has no ordering, how can it be "out of synch" --
whatever that means.

4) "So I need to Update them in the order 1,2,3 etc for their
respective Invoice number"

Unh? SQL is a set-oriented language. Updates are done in whole sets
and not row-at-a-time. You are confusing tables with magnetic tape
files. The usual patter is like this skeleton:

CREATE TABLE Invoices
(invoice_nbr INTEGER NOT NULL PRIMARY KEY,
..);

(CREATE TABLE InvoiceDetails
(invoice_nbr INTEGER NOT NULL
REFERENCES Invoices (invoice_nbr),
sku CHAR(10) NOT NULL
REFERENCES Inventory (sku),
PRIMARY KEY (invoice_nbr, sku),
order_qty INTEGER NOT NULL
CHECK(order_qty > 0),
..);

There is no mention of the paper (or video) order form lines in the
RDBMS. They are physical and the RDBMS is logical. You are really
missing basic concepts and need to get help.
From: Ed Murphy on 1 Feb 2008 21:36
--CELKO-- wrote:

> 4) "So I need to Update them in the order 1,2,3 etc for their
> respective Invoice number"
>
> Unh? SQL is a set-oriented language. Updates are done in whole sets
> and not row-at-a-time. You are confusing tables with magnetic tape
> files. The usual patter is like this skeleton:
>
> CREATE TABLE Invoices
> (invoice_nbr INTEGER NOT NULL PRIMARY KEY,
> ..);
>
> (CREATE TABLE InvoiceDetails
> (invoice_nbr INTEGER NOT NULL
> REFERENCES Invoices (invoice_nbr),
> sku CHAR(10) NOT NULL
> REFERENCES Inventory (sku),
> PRIMARY KEY (invoice_nbr, sku),
> order_qty INTEGER NOT NULL
> CHECK(order_qty > 0),
> ..);
>
> There is no mention of the paper (or video) order form lines in the
> RDBMS. They are physical and the RDBMS is logical. You are really
> missing basic concepts and need to get help.

Many reasonable real-world systems include some type of sort criteria
as well, e.g. OrderDetails may wish to record which line item the
customer ordered first, second, etc. - but of course there's plenty of
room for debate over how best to implement the concept.
 | 
Pages: 1
Prev: Join 4 tables - can it be done?
Next: Transfer data to web page fields