Insert into a table if row in another table exists

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Insert into a table if row in another table exists

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

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

Post by josh »

My answer would be don't do it because that is business logic, it should be built into your objects
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
Post Reply