Update and insert query in one

[Venom Rush]

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?

[nav33n]

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

[Venom Rush]

Once again nav33n saves the day...thanks ;)

[nav33n]

:P heh! yw !

[pritaeas]

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.

[nuraini]

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