CODEWITHSUNDEEP
sqloracle-database
jk4
jk4

Reputation: 15

Update Table by Matching Invoice ID

I have what is probably a simple problem. My table is a collection of invoices over the month. My ship_to_state column in my table is only showing the actual ship to state on one row on each invoice. I need to update my table so that the ship to state value appears for every GL Account line item on that invoice. Right now, it is only showing up for my 12000-99 GL Account.

Here is my current query:

UPDATE INVOICES
SET SHIP_TO_STATE = 
(SELECT SHIP_TO_STATE FROM INVOICES WHERE GL_ACCOUNT_ID = '12000-99' 
AND INVOICES.INVOICE_ID = INVOICES.INVOICE_ID);

Here is my table:

CUSTOMER_ID INVOICE_ID  ITEM_ID GL_ACCOUNT_ID   SHIP_TO_STATE
Customer1   38441        ADM       46000-99 
Customer1   38441        ADM       12000-99         GA
Customer2   42809        GSD307    40100-02 
Customer2   42809        GSD310    40100-01 
Customer2   42809        GSW311    40100-01 
Customer2   42809        GSD200    40100-01 
Customer2   42809        FSR270    40100-02 
Customer2   42809        ADM       12000-99         WA
Customer3   42810        GSS10-100 40100-01 
Customer3   42810        GSS10-500 40100-05 
Customer3   42810        GSD210    40100-01 
Customer3   42810        ADM       45100-99 
Customer3   42810        ADM       12000-99         AL
Customer4   42811        PSG550-L  40100-02 
Customer4   42811        PSG550-M  40100-02 
Customer4   42811        ADM       12000-99         GA
Customer5   42812        GSS10-100 40100-01 
Customer5   42812        GSS10-350 40100-05 
Customer5   42812        GSD200    40100-01 
Customer5   42812        ADM       12000-99         NC
Customer6   42813        FSF105    40100-02 
Customer6   42813        FSF135    40100-02 
Customer6   42813        GSD310    40100-01 
Customer6   42813        GSW311    40100-01 
Customer6   42813        GSD190    40100-01 
Customer6   42813        GSW312    40100-01 
Customer6   42813        ADM       45100-99 
Customer6   42813        ADM       12000-99        TX

I am looking forward to your feedback. Thanks.

Upvotes: 0

Views: 202

Answers (4)

MT0
MT0

Reputation: 168232

Oracle Setup:

CREATE TABLE INVOICES ( CUSTOMER_ID, INVOICE_ID, ITEM_ID, GL_ACCOUNT_ID, SHIP_TO_STATE ) AS
SELECT 'Customer1', 38441, 'ADM',       '46000-99', NULL FROM DUAL UNION ALL
SELECT 'Customer1', 38441, 'ADM',       '12000-99', 'GA' FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'GSD307',    '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'GSD310',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'GSW311',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'GSD200',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'FSR270',    '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer2', 42809, 'ADM',       '12000-99', 'WA' FROM DUAL UNION ALL
SELECT 'Customer3', 42810, 'GSS10-100', '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer3', 42810, 'GSS10-500', '40100-05', NULL FROM DUAL UNION ALL
SELECT 'Customer3', 42810, 'GSD210',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer3', 42810, 'ADM',       '45100-99', NULL FROM DUAL UNION ALL
SELECT 'Customer3', 42810, 'ADM',       '12000-99', 'AL' FROM DUAL UNION ALL
SELECT 'Customer4', 42811, 'PSG550-L',  '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer4', 42811, 'PSG550-M',  '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer4', 42811, 'ADM',       '12000-99', 'GA' FROM DUAL UNION ALL
SELECT 'Customer5', 42812, 'GSS10-100', '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer5', 42812, 'GSS10-350', '40100-05', NULL FROM DUAL UNION ALL
SELECT 'Customer5', 42812, 'GSD200',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer5', 42812, 'ADM',       '12000-99', 'NC' FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'FSF105',    '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'FSF135',    '40100-02', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'GSD310',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'GSW311',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'GSD190',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'GSW312',    '40100-01', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'ADM',       '45100-99', NULL FROM DUAL UNION ALL
SELECT 'Customer6', 42813, 'ADM',       '12000-99', 'TX' FROM DUAL;

Update:

MERGE INTO invoices dst
USING ( SELECT MAX( SHIP_TO_STATE ) OVER ( PARTITION BY invoice_id )
                 AS new_ship_to_state
        FROM   invoices ) src
ON ( src.ROWID = dst.ROWID )
WHEN MATCHED THEN
UPDATE SET ship_to_state = src.new_ship_to_state;

Result:

SELECT * FROM invoices;

CUSTOMER_ INVOICE_ID ITEM_ID   GL_ACCOU SH
--------- ---------- --------- -------- --
Customer1      38441 ADM       46000-99 GA
Customer1      38441 ADM       12000-99 GA
Customer2      42809 GSD307    40100-02 WA
Customer2      42809 GSD310    40100-01 WA
Customer2      42809 GSW311    40100-01 WA
Customer2      42809 GSD200    40100-01 WA
Customer2      42809 FSR270    40100-02 WA
Customer2      42809 ADM       12000-99 WA
Customer3      42810 GSS10-100 40100-01 AL
Customer3      42810 GSS10-500 40100-05 AL
Customer3      42810 GSD210    40100-01 AL
Customer3      42810 ADM       45100-99 AL
Customer3      42810 ADM       12000-99 AL
Customer4      42811 PSG550-L  40100-02 GA
Customer4      42811 PSG550-M  40100-02 GA
Customer4      42811 ADM       12000-99 GA
Customer5      42812 GSS10-100 40100-01 NC
Customer5      42812 GSS10-350 40100-05 NC
Customer5      42812 GSD200    40100-01 NC
Customer5      42812 ADM       12000-99 NC
Customer6      42813 FSF105    40100-02 TX
Customer6      42813 FSF135    40100-02 TX
Customer6      42813 GSD310    40100-01 TX
Customer6      42813 GSW311    40100-01 TX
Customer6      42813 GSD190    40100-01 TX
Customer6      42813 GSW312    40100-01 TX
Customer6      42813 ADM       45100-99 TX
Customer6      42813 ADM       12000-99 TX

Upvotes: 0

user5683823
user5683823

Reputation:

You could use the MERGE statement (the syntax is a bit clearer than updating through a join or through correlated subqueries). The solution below assumes that in the current table you have no more than one non-null value for each invoice_id.

Syntax may a bit off (you didn't provide data for testing*). *By which I mean, no CREATE TABLE and INSERT statements.

merge into invoices i
  using ( select invoice_id, ship_to_state 
          from   invoices 
          where  ship_to_state is not null )  s
    on (i.invoice_id = s.invoice_id)
when matched then update
  set i.ship_to_state = s.ship_to_state
    where i.ship_to_state is null

Upvotes: 0

htf
htf

Reputation: 143

UPDATE INVOICES
SET SHIP_TO_STATE = 
(SELECT MAX(SHIP_TO_STATE) FROM INVOICES i WHERE i.INVOICE_ID = INVOICES.INVOICE_ID);

This returns only one record per invoice id and fixes the aliasing issue mentioned elsewhere. If there is no ship_to_state value, it will update with NULL. If there is more than one, it will pick the later one alphabetically - there are ways around that if it's an issue.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270361

You are using a correlated subquery. You need a table alias or two:

UPDATE INVOICES
    SET SHIP_TO_STATE = (SELECT i.SHIP_TO_STATE
                         FROM INVOICES i
                         WHERE i.GL_ACCOUNT_ID = '12000-99' AND
                               i.INVOICE_ID = INVOICES.INVOICE_ID
                        );

Shouldn't you be storing this information in a table that has one row per invoice_id? It is strange that such an id would be repeated in a table called INVOICES.

Upvotes: 0

Related Questions