CODEWITHSUNDEEP
sql-server
chadgage
chadgage

Reputation: 21

SQL - Updating field matching fields from 2 other tables

Maybe the coffee hasn't kicked in but I'm having trouble wrapping my head around how to accomplish a SQL update. Essentially, I have 3 tables and I'm attempting to update a field in table1 with data in table2 based off criteria in table 3.

My 3 tables with sample data:

tblInvoiceDetail

keyID     InvoiceNumber      ItemID      Qty       Price
--------------------------------------------------------
1            200123          100001       4         400
2            200123          100002       1          10
3            200321          100001       1         100
4            200555          100002       2          20
5            200444          100003       4          20

tblInvoices

keyID      InvoiceNumber      InvoiceDate      CustomerID     InvoiceTotal
--------------------------------------------------------------------------
1            200123             3/15/19          456123           410
2            200321             5/31/19          123456           100
3            200555             6/30/19          111222           120
4            200444             6/31/19          111222            20

tblItemUpdate

keyID       OldItem        NewItem
----------------------------------
1           100001         999001
2           100002         999002
3           100003         999003

Here's an UPDATE statement that I tried but it would not update all the records I expected to.

UPDATE 
    tblInvoiceDetail
SET 
    tblInvoiceDetail.ItemID = tblItemUpdate.NewItem
FROM 
    tblInvoiceDetail
INNER JOIN 
    tblItemUpdate ON tblInvoiceDetail.ItemID = tblItemUpdate.OldItem
INNER JOIN 
    tblInvoices ON tblInvoiceDetail.InvoiceNumber = tblInvoices.InvoiceNumber
WHERE
    tblInvoices.InvoiceDate < '2019-06-25'

I suspect the problem is in my JOINs so it is not matching all the records I'm wanting.

I want to change the ItemID in tblInvoiceDetail and I want it to find the match in tblItemUpdate.OldItem and change it to the value in tblItemUpdate.NewItem. However, I only want to update the ItemID if tblInvoices.InvoiceDate is prior to 6/25/19.

So, with my sample data in tblInvoiceDetail, only keyIDs 1, 2, and 3 would need to be updated.

I have a lot of records in my live database so I can point to 1 particular thing that didn't work. I just found several invoices that I thought should have changed but didn't.

Upvotes: 1

Views: 72

Answers (2)

shawnt00
shawnt00

Reputation: 17915

The standard way to write this is by using subqueries:

UPDATE tblInvoiceDetail
SET ItemID = (SELECT NewItem FROM tblItemUpdate WHERE OldItem = tblInvoiceDetail.ItemID)
WHERE InvoiceNumber IN (
    SELECT id.InvoiceNumber
    FROM tblInvoiceDetail AS id INNER JOIN tblItemUpdate AS iu ON id.ItemID = iu.OldItem
    WHERE id.InvoiceDate < CAST('2019-06-25' AS DATE)
);

Ezlo already pointed out the problem when joining to the updated table created multiple rows.

EDIT:

Looking closer at your data I also see that you have a bigger problem matching up ItemIDs with the correct invoices. The primary key in tblInvoiceDetail is a compound of both InvoiceNumber and ItemID yet you don't appear to have that information in the list from tblItemUpdate.

Since your sample data includes an invalid date of June 31 it's natural to wonder if a string comparison issue is at play. The primary key issue would potentially rope in more invoices than intended so it wouldn't explain your problem described as fewer rows updating than expected.

Upvotes: 1

EzLo
EzLo

Reputation: 14189

I want to change the ItemID in tblInvoiceDetail and I want it to find the match in tblItemUpdate.OldItem and change it to the value in tblItemUpdate.NewItem

So far:

UPDATE D SET 
    ItemID = U.NewItem
FROM 
    tblInvoiceDetail AS D
    INNER JOIN tblItemUpdate AS U ON D.ItemID = U.OldItem

However, I only want to update the ItemID if tblInvoices.InvoiceDate is prior to 6/25/19.

You can check for existence with EXISTS:

UPDATE D SET 
    ItemID = U.NewItem
FROM 
    tblInvoiceDetail AS D
    INNER JOIN tblItemUpdate AS U ON D.ItemID = U.OldItem
WHERE
    EXISTS (SELECT NULL FROM tblInvoices AS I WHERE 
            I.InvoiceNumber = D.InvoiceNumber AND -- Link the invoice to update (D) to the invoice table (I)
            I.InvoiceDate < '2019-06-25')

Be careful with your InvoiceDate data type. It should be DATE (or DATETIME) and not VARCHAR, because it will be bring trouble whenever you compare it against a value otherwise ('6/25/19' and '2019-06-25' are both dates but they are completely different strings).

Also beware of the existence of multiple rows with the same OldItem value on tblItemUpdate, as you might be joining a particular row from tblInvoiceDetail against N from the tblItemUpdate in which case the final NewItem value that will be updated is indetermined.

Upvotes: 0

Related Questions