izzy84 izzy84 - 3 months ago 7
MySQL Question

Inserting rows from one table into another if they don't already exist in MySQL

I am trying to INSERT a date from Table A into Table B when the date does not already exist in Table B. I used the NOT EXISTS function, but the dates do not appear to be populating as I would have hoped.

To give additional context, Table B is a General Ledger, which can have zero or multiple transactions in a single day and Table A is a Balance Sheet with one entry for each day regardless of whether there was a transaction in the General Ledger.

Here is the SQL code I used:

INSERT INTO gl_account_type_descriptions
(Company_Name, `Account ID`, `Date`)
SELECT Company_Name,
`Account ID`,
`Date`
FROM all_balance_sheet
WHERE NOT EXISTS (
SELECT DISTINCT Company_Name, Account ID`, `Date`
FROM gl_account_type_descriptions
)

Answer

You're missing conditions inside the NOT EXISTS part:

INSERT INTO gl_account_type_descriptions 
  (Company_Name, `Account ID`, `Date`)
SELECT Company_Name, `Account ID`, `Date`
FROM all_balance_sheet AS bal
WHERE NOT EXISTS (
  SELECT Company_Name, `Account ID`, `Date`
  FROM gl_account_type_descriptions AS acc
  WHERE bal.Company_name = acc.Company_name
    AND bal.`Account ID` = acc.`Account ID`
    AND bal.`Date` = acc.`Date`
  )

I've also removed the superfluous DISTINCT clause, since it's not needed and would slow down your query.

Comments