CODEWITHSUNDEEP
mysqlsqlsql-update
stwhite
stwhite

Reputation: 3275

Mysql: Toggle value of int field under two conditions for UPDATE query

What I'm looking to do is insert a record, then deactivate previous records with the same ID because they will no longer be in use. However, I'm looking to do this in the simplest way possible. Deleting the record really isn't an option.

Attempted order of operations:

  1. Insert with active inUse value inUse = 1
  2. Update the following records for the same ID that are no longer in use: inUse = 0

My first thought was to run this query:

UPDATE page_tags SET inUse = IF(inUse = 1, 0, 1) WHERE page_id = 23678459 AND tag_id NOT IN (10, 4);

The only problem with this query is that if it's run again, it will toggle all of those deactivated values back to 1. I need all of the tags for the specific ID to only toggle back if they are being targeted by the WHERE statement.

Upvotes: 0

Views: 821

Answers (2)

stwhite
stwhite

Reputation: 3275

@John P has a decent answer, however his answer requires the use of triggers. That to me seems to be more than needed to solve the problem at hand. The current working solution is:

Create an Unique Index on page_tags.page_id and page_tags.tag_id.

Update all rows where the *page_id = 234234*: 

UPDATE page_tags SET inUse = 0 WHERE page_id = 234234

Insert tags: 

INSERT INTO page_tags (page_id, tag_id, inUse) VALUES (234234, 49343, 1) ON DUPLICATE KEY UPDATE inUse = 1

Upvotes: 0

John P
John P

Reputation: 15245

Sounds like a job for trigger. Something like will perhaps do (pseudocode)?

UPDATE for handling reuse of previuos tags:

Do your insert/update:

INSERT INTO ... ON DUPLICATE KEY UPDATE

Then use two triggers, one for inserts and one for updates.

CREATE TRIGGER tr_inuse_insert BEFORE INSERT ON page_tags
  FOR EACH ROW BEGIN
    UPDATE page_tags SET inuse=0 WHERE page_id = NEW.page_id;
  END;

CREATE TRIGGER tr_inuse_update BEFORE UPDATE ON page_tags
  FOR EACH ROW BEGIN
    UPDATE page_tags SET inuse=0 WHERE page_id = NEW.page_id;
  END;

Upvotes: 1

Related Questions