CODEWITHSUNDEEP
mysqlsqltransactions
Jim Johnson
Jim Johnson

Reputation: 2609

Transaction with One SQL Statement

I'm learning how to program by creating a blog, and one of the requirements that I have is to increment the "count" column of a post category whenever a new blog post is created.

As for now, I have to use the following statements to accomplish this task: 

INSERT INTO posts (..., ..., category_id) VALUES (..., ..., 5);
UPDATE categories SET count = count + 1 WHERE id = 5

Is there any way for me to combine these two into a single SQL statement? If not, is there a way to "automate" the increment of the count field whenever I created a new post with that category?

Upvotes: 0

Views: 99

Answers (2)

Justin Swartsel
Justin Swartsel

Reputation: 3431

You can automate this by using a trigger.

CREATE TRIGGER myPostsTrigger AFTER INSERT
ON posts
FOR EACH ROW
BEGIN
    UPDATE categories SET count = count + 1 WHERE id = NEW.category_id
END

Then, when you insert a row into posts (INSERT INTO posts (..., ..., category_id) VALUES (..., ..., 5);), it will automatically increment your count.

Upvotes: 1

Marc B
Marc B

Reputation: 360662

you can't combine the statements, because they're doing two different operations on different tables. But wrapping them in a transaction effectively makes them an atomic pseudo-single statement:

start transaction;
insert into posts blah blah blah;
update categories blah blah blah;
commit;

You could update thigns by using a trigger on the posts table. Any time a record is inserted or delete, you'd have the trigger run the update query.

Upvotes: 2

Related Questions