Update one table using another

Databases
1

Hi
im using the below to update a field in one table using records from another
but all im getting it this error

Unknown column ‘INVOICE_Number.invoiceold’ in ‘where clause’

can anyone help me out?
here the query:


UPDATE `TEST`.`invoiceold`
SET CUSTOMER_AccNum .`invoiceold` = CUSTOMER_AccNum .`invoice`
WHERE INVOICE_Number .`invoiceold` = INVOICE_Number .`invoice`
AND COMPANY_Number .`invoiceold` = COMPANY_Number .`invoice`

Thanks

2

updating one table from another requires a join

also, your column syntax is wrong, it should be tablename.columnname, not columnname.tablename

UPDATE invoiceold
INNER
  JOIN invoice
    ON invoice.INVOICE_Number = invoiceold.INVOICE_Number
   AND invoice.COMPANY_Number = invoiceold.COMPANY_Number 
SET    invoiceold.CUSTOMER_AccNum = invoice.CUSTOMER_AccNum
3

Hi r937

Thanks that worked perfect