0

Hi there

I'd like to know if it's possible to combine an update and insert query into one query. What I'd like to do is insert new data into one table and update a colum in another table at the same time.

For example, there are 3 different baskets (puppy basket, kitten basket and chick basket). I add a brown puppy to the puppy basket so I'd update the the basket table column that keeps track of the number of puppies in the puppy basket and insert into the puppy table the new puppy and it's colour.

Would I join the 2 queries with AND?

4
Contributors
5
Replies
18
Views
9 Years
Discussion Span
Last Post by nuraini
1

As far as I know, you can't have insert and update in the same query. The only case where you can have insert and update in the same query is when you use on duplicate clause . If you are using php to insert values to the table, you can do it without any problem.

$insert_query="insert into puppybasket (number_of_puppies,color) values ('val1','val2')"; //val1 has the number and val2 has the color
mysql_query($insert_query);
$update_query="update basket set total=total+'val1' where condition"; // total will be updated to total+number of new puppies added.
mysql_query($update_query);

Cheers,
Naveen

Votes + Comments
Exactly what I needed to know ;)
0
INSERT INTO table (field) VALUES (value) ON DUPLICATE KEY UPDATE field=value

You need to specify keys to get this to work. See the mysql docs for more info. It is quite powerful and fast.

0

pritaeas, you also help me on this.. just use ON DUPLICATE KEY...
THANKS A LOT....

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.