Insert into table2 only if a similar record exists in table1

Question

I have 2 tables that are not similar. Is there some way by which I can insert a record into table2 only if a record with a similar value was found in table1? I'm doing all this with php pdo and mysql

For Example: Lets say table1 has values like:

id
--
1
2
3
4
6

Then:

insert into table2 (id) values (3) // Will work, because id 3 exists in table1
insert into table2 (id) values (7) // Will not work, because id 7 does not exists in table1

Right now, the way I do this is to run a select count(id) where id = 3 and then if a id exists, it'll be inserted. Cumbersome!

Is there a way to do this without having to first do a select and then an insert?

Since this is just the beginning, I'm willing to make changes if something like a foreign key etc. needs to be added.

The only query being run here is insert into table2 (id) values (3). And that needs to work only if id = 3 is found in table1. The value 3 is user supplied.

http://stackoverflow.com/questions/5907206/mysql-insert-into-tbl-select-from-another-table-and-some-default-values — Nils, Aug 20 '13 at 12:44
That's quite different. I'm trying to insert a value into table2, only if a row with that id value exists in table1. Not select from table1 into table2 — Norman, Aug 20 '13 at 12:49

score 1 · Accepted Answer · answered Aug 20 '13 at 12:54

1

This will work if ID is unique in Table1:

INSERT INTO Table2 (ID)
  SELECT ID
  FROM Table1
  WHERE ID = 3;

If ID=3 exists in Table1 you'll get one row inserted. If it doesn't, nothing will be inserted.

If ID isn't unique in Table1 just add DISTINCT to the SELECT:

INSERT INTO Table2 (ID)
  SELECT DISTINCT ID
  FROM Table1
  WHERE ID = 3;

answered Aug 20 '13 at 12:54

Ed Gibbs

25,924
4
46
69

Good. I'd go with the first example then. With a unique or PK constraint it will be fast. If you need to insert other values you can just add them as constants to the `SELECT` list, for example: `INSERT INTO Table2 (ID, Name) SELECT ID, 'Norman' FROM Table1 WHERE ID = 3` – Ed Gibbs Aug 20 '13 at 12:58

score 0 · Answer 2 · answered Aug 20 '13 at 12:54

Create a trigger

DELIMITER $$
CREATE
DEFINER = CURRENT_USER
TRIGGER `table2_check` BEFORE INSERT
ON table1
FOR EACH ROW BEGIN
if exists (select id from table1 where id = new.id) then
    insert into table2 (id) values (new.id);
end if;
END$$
DELIMITER ;

Insert into table2 only if a similar record exists in table1

2 Answers2