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?
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';
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;
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.
$result = mysql_query("SELECT * FROM t1 WHERE v='test'");
if(mysql_num_rows($result) == 0){
mysql_query("INSERT INTO t2 (v) VALUES ('test')");
}
Haven't been using mysql for a while so these functions are deprecated, but this is how I should do it.
Kinda ghetto.. but works. Assumes that you are inserting a static value into 'col'
INSERT INTO t1 (col)
SELECT 'test'
FROM t2
WHERE (SELECT count(*) FROM t2 WHERE v='test') = 0;
