CODEWITHSUNDEEP
mysqldatabase
BlackHatSamurai
BlackHatSamurai

Reputation: 23493

Is it possible to increment a database value without making a query to the database?

I am using a mysql database and have table called timeslots the columns for timeslot are time_slot_id, name, openings, and appointments. What I want to do is update the value of appointments by 1 when I add an appointment to another table.

One way to do this, would be to make a query to the database and get the value of appointments and then add one to it. However, would rather just update the table without having to make a request to the database to get this information.

I'm wondering if this is possible; if so, how?

Upvotes: 0

Views: 56

Answers (2)

Ezequiel Muns
Ezequiel Muns

Reputation: 7752

You can do this:

START TRANSACTION;

UPDATE timeslot
SET appointments = appointments + 1
WHERE time_slot_id = ?;

INSERT INTO other_table(..., time_slot_id) VALUES (..., ?);

COMMIT;

Upvotes: 1

medina
medina

Reputation: 8169

Blaine, trigger is the answer, just create something like that:

CREATE TRIGGER update_timeslots
AFTER INSERT ON my_table_appointments
FOR EACH ROW
BEGIN
  UPDATE timeslots 
  SET appointments = appointments + 1 
  WHERE timeslots.time_slot_id = NEW.time_slot_id
END;

I hope it helps =)

Upvotes: 1

Related Questions