CODEWITHSUNDEEP
mysqlsql-insert
izzy84
izzy84

Reputation: 259

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
  )

Upvotes: 0

Views: 71

Answers (2)

Ravi
Ravi

Reputation: 31407

If your AccountId column is Unique. Then, you might use below sql

INSERT IGNORE INTO gl_account_type_descriptions (`Account ID`,Company_Name, `Date`)
SELECT DISTINCT `Account ID`,Company_Name, `Date` FROM all_balance_sheet;

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 1

Related Questions