inserting records in a MySQL table depending on existing values in another table

Question

Hi guys,

I am using MySQL. I want to insert some records in a table provided that they do not exist in another table. So I want to perform something like this:

INSERT INTO sales (name, id)
SELECT name, id FROM sales_h 
WHERE name AND id NOT IN (SELECT name, id FROM T_sales);

The problem is that MySQL does not allow this kind of syntax (something to do with the where clause...) I tried using CONCAT but with no result.

Any clue??

Answer 1

You might look into the ON DUPLICATE clause for MySql

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

Answer 2

You can't say WHERE name AND id NOT IN... you need a separate clause for each. But do you have id as the primary key in table T_sales? If so, then that's all you need to check for, sp something like:

INSERT INTO sales (name, id) 
(SELECT name, id FROM sales_h WHERE id NOT IN (SELECT id FROM T_sales));

EDIT

Hmmm from other people's answers it might be best to ignore me as they seem to know more :-)

Answer 3

INSERT
INTO    sales (name, id)
SELECT  name, id
FROM    sales_h
WHERE  (name, id) NOT IN
       (
       SELECT name, id
       FROM t_sales
       )

Answer 4

The problem is your WHERE.

It's not reading as you are. You're reading it as "WHERE (name and id) NOT IN (...)", whereas it's reading it as "WHERE name (isn't null) AND (id NOT IN (...))"... if you get what I mean.

Change to this: ROW(name, id) NOT IN (SELECT name, id FROM T_sales);

Answer 5

Have you tried the EXISTS syntax?

INSERT INTO sales (name, id) 
SELECT name, id 
FROM sales_h 
WHERE NOT EXISTS (SELECT * FROM T_sales WHERE T_sales.name = sales_h.name AND T_sales.id = sales_h.id);

Answer 6

MySQL allows you to use a tuple like a variable:

This is your statement:

INSERT INTO sales (name, id)
SELECT name, id FROM sales_h 
WHERE name AND id NOT IN (SELECT name, id FROM T_sales);

The problem is "name AND Id". Turn that into a tuple:

INSERT INTO sales (name, id)
SELECT name, id FROM sales_h 
WHERE (name, id) NOT IN (SELECT name, id FROM T_sales);

Personally, I don't like this much for two reasons: the tuple as variable doesn't work (or works differently) on other RDBMSes, and IN tends to perform poorly in many situations, so I like to make it a habit not to use IN.

As jhonny-d-cano-leftware states (I've upmodded him), I'd use a where not exists:

INSERT INTO sales (name, id)
SELECT name, id FROM sales_h a
WHERE not exists (
 SELECT * 
 FROM T_sales b 
 where b.id = a.id and b.name = a.name);

Answer 7

INSERT INTO table_name(column1, column2) SELECT "value one", "value two" FROM DUAL
WHERE NOT EXISTS(
    SELECT column1 FROM table_name
    WHERE column1 = "value one"
    AND column2 = "value two" LIMIT 1
)