Member Avatar for 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?

  • 4 Contributors
  • 5 Replies
  • 10K Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by nuraini

Dani AI

Generated

You cannot chain two separate DML statements with AND. To do an insert into one table and an update to another safely, run two statements inside a single transaction so the basket count and the new puppy row are applied (or rolled back) together. Ensure the tables use InnoDB so row-level locks and ACID semantics apply.

START TRANSACTION;

INSERT INTO puppies (basket_id, color) VALUES (?, ?);
UPDATE baskets
SET puppy_count = puppy_count + 1
WHERE id = ?;

COMMIT;
-- On error: ROLLBACK;

In application code, check affected-row counts and rollback if any step fails. This avoids race conditions that can occur under concurrent inserts. See START TRANSACTION, COMMIT, and ROLLBACK and InnoDB transaction model.

Also consider eliminating the manual counter. You can either:

  • Compute the count on demand with SELECT COUNT(*) FROM puppies WHERE basket_id = ? (properly indexed), or
  • Maintain the counter automatically with triggers so application code stays simple:
CREATE TRIGGER puppies_ai
AFTER INSERT ON puppies
FOR EACH ROW
UPDATE baskets
SET puppy_count = puppy_count + 1
WHERE id = NEW.basket_id;

Add matching triggers for DELETE/UPDATE to keep totals accurate. Triggers centralize the logic and reduce the chance of forgetting to update the count. See MySQL triggers.

Recommended Answers

All 5 Replies

Member Avatar for 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

commented: Exactly what I needed to know ;) +1
Member Avatar for Venom Rush

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

Member Avatar for nav33n

:P heh! yw !

Member Avatar for 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.

Member Avatar for nuraini

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.