Page 1 of 1

Insert into a table if row in another table exists

Posted: Sun Mar 29, 2009 2:41 pm
by JellyFish
How would I write a MySQL statement that will insert a row into one table only if a row in another table exists. I tried something like this:

Code: Select all

 
IF EXIST(SELECT * FROM `posts` WHERE `post_id` = $id) THEN INSERT INTO `post_flags` (`post_id`, `ip_num`, `reason`) VALUES ($id, INET_ATON('$ip'), '$reason');
 
But I get an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXIST(SELECT * FROM `posts` WHERE `post_id` = 6) THEN INSERT INTO `post_flags' at line 1
How can I insert a row into a table only if a row in another table exists?

I need to be able to do this because I only would like to insert flags into the table post_flags if there is a corresponding post in the posts table.

Re: Insert into a table if row in another table exists

Posted: Tue Mar 31, 2009 4:49 am
by josh
My answer would be don't do it because that is business logic, it should be built into your objects

Re: Insert into a table if row in another table exists

Posted: Tue Mar 31, 2009 6:14 am
by Eran

Code: Select all

INSERT INTO `post_flags` (`post_id`, `ip_num`, `reason`) SELECT `post_id`,INET_ATON('$ip'), '$reason' FROM `posts` WHERE `post_id` = $id