mysql insert if value not exist in another table
Solution 1
You need to use some type of INSERT...SELECT
query.
Update (after clarification): For example, here is how to insert a row in t2
if a corresponding row do not already exist in t1
:
INSERT INTO t2 (v)
SELECT temp.candidate
FROM (SELECT 'test' AS candidate) temp
LEFT JOIN t1 ON t1.v = temp.candidate
WHERE t1.v IS NULL
To insert multiple rows with the same query, I 'm afraid there is nothing better than
INSERT INTO t2 (v)
SELECT temp.candidate
FROM (
SELECT 'test1' AS candidate
UNION SELECT 'test2'
UNION SELECT 'test3' -- etc
) temp
LEFT JOIN t1 ON t1.v = temp.candidate
WHERE t1.v IS NULL
Original answer
For example, this will take other_column
from all rows from table1
that satisfy the WHERE
clause and insert rows into table2
with the values used as column_name
. It will ignore duplicate key errors.
INSERT IGNORE INTO table2 (column_name)
SELECT table1.other_column
FROM table1 WHERE table1.something == 'filter';
Solution 2
for insertion of Multiple columns you can try this.
INSERT INTO table_1 (column_id,column_2,column_3,column_4,column_5)
SELECT
table_2.*
FROM
table_2
LEFT JOIN table_1 ON table_1.column_id = table_2.column_id
WHERE
table_1.column_id IS NULL;
Solution 3
INSERT IGNORE INTO table_1(col1,col2,col3,col4)
SELECT table_2.*
FROM table_2;
The above statement does the perfect job in your scenario.
- Line 1: States the table where you want the data to be replicated whilst ignoring any key constraints.
- Line 2: selects the table and columns where data should come from
- Line 3: states the table from where data is to be replicated from
Related videos on Youtube
Comments
-
Ariyan over 1 year
I have two tables that store value as
VARCHAR
.
I'm populating table and I want just insert values in one of tables if they are not exist in other table.
Something like:INSERT IF IS EMPTY(SELECT * FROM t1 where v='test') INTO t2 (v) VALUES ('test')
How Can I do that?
-
Ariyan over 11 yearsBut this inserts into table2 and gets values from table1 ! While I want to insert if the value doesn't exists in table1! Please See my edit on question, thanks
-
Waihon Yew over 11 years@4r1y4n: If the value doesn't exist in table1 because you say so, and it doesn't exist in table2 because we are trying to insert it there, then where does it exist?
-
Ariyan over 11 yearsIn SQL query itself (See my psudo query in question - 'test' is the value)! I just want insert value only in one of tables and not both (t1 XOR t2)
-
Ariyan over 11 yearsThis is PHP and not pure MySQL! of course this works but I want to do it using an MySQL query
-
yoeriboven over 11 yearsOkay, with my last sentence I meant it'd be better to use PDO or MySQLi. I see I didn't get your question and unfortunately don't know the answer. Sorry!
-
BurninLeo over 2 yearsAs of MySQL 8.0.19 you won't need a temporary table to define the data, but use VALUE instead (dev.mysql.com/doc/refman/8.0/en/values.html).
-
BurninLeo over 2 yearsPractical note: This works fine to insert a small number of values. But when inserting a few hundret values, and checking this against a few 100k existing values, a 200x250.000 JOIN makes the query painfully slow.