Update Records in a table from another table

[sharapov]

I have a database with two tables (Table 1 called: "DealDocs" and Table 2 called: "TRInvestmentListing"). There are several fields that match in each table, but most of them are not (ex: field "Entity, InvestID, description" exist in both tables, field "Cusip" only exiists in "TRInvestmentListing" table). I need to create a query with the following condition: if records for the field "Entity" and field "InvestID" (both fields) from table "TRInvestmentListing" don't exist in "DealDocs" table and "Cusip" field (from table "TRInvestmentListing") is empty, copy those records to DealDocs table.

Can anybody help me out with that query?

 

[PHV]

An append query like this (SQLcode) ?
INSERT INTO DealDocs (Entity, InvestID, description)
SELECT T.Entity, T.InvestID, T.description
FROM TRInvestmentListing AS T LEFT JOIN DealDocs AS D
ON T.Entity = D.Entity AND T.InvestID = D.InvestID
WHERE D.Entity Is Null AND D.InvestID Is Null AND Trim(T.Cusip & "") = ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886