MySQL: Insert row on table2 if row in table1 exists

Question

I'm trying to set up a MySQL query that will insert a row into table2 if a row in table1 exist already, otherwise it will just insert the row into table1.

I need to find a way to adapt the following query into inserting a row into table2 with the existing row's id.

INSERT INTO table1 (host, path) VALUES ('youtube.com', '/watch') IF NOT
EXISTS ( SELECT * FROM table1 WHERE host='youtube.com' AND path='/watch' LIMIT 1);

Something kind of like this:

INSERT ... IF NOT EXISTS(..) ELSE INSERT INTO table2 (table1_id) VALUES(row.id);

Except I don't know the syntax for this.

Answer 1

You could achieve this with INSERT INTO SELECT I believe.

INSERT INTO
    table1 (host, path)
SELECT
    table2.host, table2.path
FROM
    table2
WHERE
    table2.host='youtube.com' AND path='/watch'
LIMIT 1;

Disclaimer: untested.

Answer 2

This is the solution I came up with:

CREATE TRIGGER onUpdate BEFORE UPDATE ON table1 FOR EACH ROW INSERT INTO table2 SET t1_row = OLD.id

Answer 3

You can use a trigger, a unique or primary key on table1, and INSERT IGNORE.

If you insert in table1 a value which already exists, INSERT IGNORE will not return a duplicate key error. The trigger will just check if the value already exists in table1 and insert it into table2. If you need, you can also add an unique key on table2 and use INSERT IGNORE in the trigger.

God luck!

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE `table2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

insert ignore into table1(id) values(1);
select * from table1;
select * from table2;