CODEWITHSUNDEEP
mysql
Niet the Dark Absol
Niet the Dark Absol

Reputation: 324780

Any way to merge delete/insert queries?

I have the following two queries being run when the assignment of staff on a team changes:

delete from `team_staff` where `team_id`=5
insert into `team_staff` (`team_id`,`staff_id`) values (5,1),(5,2)

In this case, I'm changing the staff roster of Team 5 to be Staff 1 and Staff 2. Anyone else who was assigned is now unassigned.

It works, but I like reducing the number of queries being run - it's good for the query cache and such.

Usually I would do something like this:

insert ignore into `team_staff` (`team_id`,`staff_id`) values (5,1),(5,2)

However this won't remove any staff who aren't Staff 1 or Staff 2.

Is there any way to do this in a single query, or am I stuck with two?

Upvotes: 0

Views: 44

Answers (1)

user1932079
user1932079

Reputation:

There isn't an insert & delete MySQL function, but that doesn't mean you're out of luck. Consider these options:

1) Your current method deletes without reference to whether your insert will re-insert something you've deleted, creating more work that doesn't really need to be done. Also, deleting all those rows all of the time will require some OPTIMIZE commands to be run periodically. You can optimize your delete command to ignore those you'll insert, but will still need to optimize periodically.

2) Since your teams are numbered, if there are only a few team numbers ever used, you may want to look at using a SET on the staff table. Then you will only need to execute an update command.

3) If SET is too small, you may want to look at one of the INT columns and use binary numbers &'d together. For example, team 1 is 2^1, team 2 is 2^2, ... Then when you update the INT column on the staff table for the team membership, you would update it to represent the value of the teams they were part of (e.g. UPDATE staff SET teams=2^1 & 2^2 & 2^3). This would allow you to search for matches using only numbers.

Those are the potential optimizations which come to my mind, though none of them directly answer your specific question, they do address the intent.

Upvotes: 1

Related Questions