How to update table column based on data from the same table (with joins)

Question

I'm trying to update every row of my invoices table to hold the tax amount of that invoice. This is calculated by getting the tax percentage associated with the vendor and multiplying it by the invoice amount (obviously). My problem is that I'm trying to update the table with data from the same table joined with others. Currently, my query shown below gives errors of:

Error Code: 1093. Table 'tblVendorInvoices' is specified twice, both as a target for 'UPDATE' and as a separate source for data

when I remove the WHERE statement &

Error Code: 1054. Unknown column 'a.VENDORINVOICEID' in 'where clause'

With the WHERE.

Here is my query:

UPDATE tblVendorInvoices SET VdrTaxAmount = 
(SELECT round(VdrInvoiceAmount*TaxAmount,2) FROM tblVendorInvoices a
LEFT JOIN tblVendors ON a.VendorName = tblVendors.VendorID
LEFT JOIN tblTax on tblVendors.vdrtaxid = tblTax.TAXID)
WHERE a.VENDORINVOICEID = tblVendorInvoices.VENDORINVOICEID;

Check out https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source — HereGoes, Jul 10 '19 at 17:12

aseem bhartiya · Answer 1 · 2019-07-10T18:10:33.117

0

This should work

UPDATE tblVendorInvoices SET VdrTaxAmount = c.Amount
from
(SELECT VENDORINVOICEID,round(VdrInvoiceAmount*TaxAmount,2) Amount FROM tblVendorInvoices a
LEFT JOIN tblVendors ON a.VendorName = tblVendors.VendorID
LEFT JOIN tblTax on tblVendors.vdrtaxid = tblTax.TAXID)c
WHERE c.VENDORINVOICEID = tblVendorInvoices.VENDORINVOICEID;

edited Jul 10 '19 at 18:10

answered Jul 10 '19 at 17:08

aseem bhartiya

94
1
10

score 0 · Answer 2 · answered Jul 10 '19 at 18:29

In MySQL, use a correlated subquery. I think this is the logic you want:

UPDATE tblVendorInvoices i
    SET VdrTaxAmount = 
        (SELECT round(VdrInvoiceAmount*TaxAmount,2)
         FROM tblVendors v JOIN
              tblTax t
              ON v.vdrtaxid = t.TAXID
         WHERE i.VendorID = v.VendorID
        );

NewCoder04 · Answer 3 · 2019-07-10T18:38:43.830

Try this

UPDATE tblVendorInvoices SET VdrTaxAmount = 
(SELECT round(VdrInvoiceAmount*TaxAmount,2) 
FROM tblVendorInvoices as A
LEFT JOIN tblVendors as V ON a.VendorName = V.VendorID
LEFT JOIN tblTax on V.vdrtaxid = tblTax.TAXID)
WHERE A.VENDORINVOICEID = V.VENDORINVOICEID;

Your getting that error because you have not distinguished what table and column to use in the where statement: WHERE a.VENDORINVOICEID = tblVendorInvoices.VENDORINVOICEID; You are using the same table and column. You might also look at your first join statement because you might be trying to join a string and a INT and this will not work. You need to join on two columns that have the same data type. I don't know what type of data each of your columns has.

How to update table column based on data from the same table (with joins)

3 Answers3