SQL - Access conditional update

Question

I have a table named POS_File_Detail. I would like to update Line_Number based on the count of the Invoice_Number and increment by 1 for every duplicate. So basically if I have three duplicate entries with the same Invoice_Number it will update Line_Number with what duplicate number it is. Something like this

    Invoice_Number | Line_Number | Description
    0123456          1             Pepsi
    0123456          2             Chips
    0123499          1             Hot dogs
    0123456          3             Mustard

The primary key for this table is an autonumber field named Record_ID.

I'm not sure how to put this in correct syntax, but hopefully this will show you what I'm trying to do

    UPDATE POS_File_Detail 
    SET POS_File_Detail.Line_Number = Line_Number +1
    WHERE COUNT(Invoice_Number)>1;

What is the primary key of POS_File_Detail? That's a table, right (not a database as is mentioned)? — Tim Lehner, Apr 02 '12 at 16:35
I'm completely sorry POS_File_Detail is my table not my database. The primary key is an auto increment Record_ID — Craig, Apr 02 '12 at 17:15

HansUp · Accepted Answer · 2012-04-02T17:33:49.547

This can be really easy if you have a field or combination of fields which allow you to define the ordering of the duplicate Invoice_Number values. Without such a method, the order in which those Invoice_Number values are retrieved is arbitrary.

You have an autonumber field named "Record_ID" as POS_File_Detail primary key.

SELECT
    Invoice_Number,
    DCount("*", "POS_File_Detail",
        "Invoice_Number = '" & [Invoice_Number] & 
        "' AND Record_ID <= " & [Record_ID]) AS Line_Number
FROM POS_File_Detail;

Although you asked to store Line_Number in POS_File_Detail, I gave you a SELECT statement instead. I did that because it's almost always a mistake to store derived values in the table. Better to derive Line_Number with a query whenever you need it. That way you avoid storing redundant data and ensure Line_Number is always current despite inserts, updates and deletions to POS_File_Detail.

However if you have a sound reason to actually store Line_Number in POS_File_Detail, you can convert the SELECT to an UPDATE statement.

UPDATE POS_File_Detail
SET Line_Number = DCount("*", "POS_File_Detail",
    "Invoice_Number = '" & [Invoice_Number] &
    "' AND Record_ID <= " & [Record_ID]);

Please make sure you really have a compelling reason to store those values.

This is amazing thank you. I have no real reason to keep the data current because it is historical transaction data out of an old IBM AS/400. However, I will try to use the SELECT statement for good practice. I'll let you know how it goes, thank you! — Craig, Apr 02 '12 at 17:29
You're welcome. I just now revised the answer to include your table name and autonumber field. I made several typos and other errors earlier, but this looks correct to me now. Notice I assumed Invoice_Number is text data type. If it's not, discard the single quotes from the `DCount()` expressions. — HansUp, Apr 02 '12 at 17:39
Both of these worked perfectly, I am sticking with SELECT statement though. Thank you so much. — Craig, Apr 02 '12 at 18:50

score -1 · Answer 2 · answered Apr 02 '12 at 17:04

-1

WITH CTE(Invoice_Number,Line_Number,Description,NEW_LINE_NUMBER)
AS
(select Invoice_Number,Line_Number,Description,
ROW_NUMBER() over (partition by Invoice_Number  order by Invoice_Number) NEW_LINE_NUMBER from    POS_File_Detail)
UPDATE CTE SET Line_Number=NEW_LINE_NUMBER

answered Apr 02 '12 at 17:04

Nathan

2,705
23
28

I'll keep this in mind if I go to an actual server, thank you Nathan – Craig Apr 02 '12 at 17:31

SQL - Access conditional update

2 Answers2