Update with nested select statements
Posted on 2007-03-29
I have an insert statement that works properly. I am not well-versed by any stretch of the imagination when trying to update a field called 'transferred' inside my table (a_line2) using the same criteria below. I would appreciate someone helping me out.
Two additional notes:
1...I am having to nest these statements to insert rows in this instance because there are other rows with the same invoice_id, contact_id and property_id I don't want. The accounting_type can be different.
2....there is a unique field called accounting_id in each of the files that could be referenced if necessary.
INSERT INTO a_line2 (transaction_id, accounting_type, invoice_id, contact_id, property_id)
SELECT a.transaction_id, a.accounting_type, a.invoice_id, a.contact_id, a.property_id
FROM accounting_line AS a INNER JOIN (SELECT accounting_type, invoice_id, contact_id, property_id
WHERE (accounting_type <= 30)) AS b ON a.accounting_type = b.accounting_type AND a.invoice_id = b.invoice_id AND a.contact_id = b.contact_id AND a.property_id = b.property_id