CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 147

Mysql insert a row only if id exist in another table

I have 2 simple table

The two table are same. The p_id ai value.

I would like to insert into the table2 a row, but **only if p_id is exist in table1. This is possible? (MySQL)

INSERT INTO table1 (p_id, s_id)
SELECT * FROM (SELECT '100', '2') AS tmp
WHERE NOT EXISTS (SELECT p_id FROM table2 WHERE p_id = '100')
LIMIT 1

Upvotes: 0

Views: 3316

Answers (2)

Manish
Manish

Reputation: 3633

Try This

Insert into table2 (p_id,s_id) Select p_id,'2' as s_id FROM table1 where p_id=100 LIMIT 1

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

You can insert into a table based on any SELECT query. For example:

INSERT INTO table2 (p_id, s_id)
SELECT p_id, 2 FROM table1 WHERE p_id = 100;

If there are zero rows in table1 with the specified p_id value, this is a no-op. That is, it inserts zero rows into table2. If there is 1 row in table1 with that p_id value, it inserts into table2.

No need for LIMIT 1 because if p_id is the primary key then there is guaranteed to be only 1 or 0 rows with the given value.

Upvotes: 5

Related Questions