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?

Re: Update and insert query in one 80 80

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

commented: Exactly what I needed to know ;) +1
Re: Update and insert query in one 80 80

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

Re: Update and insert query in one 80 80

:P heh! yw !

Re: Update and insert query in one 80 80
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.

Re: Update and insert query in one 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.