UPDATE and DELETE in the same query

Jack_McSlay

I have this small problem, let's say I have a DB like this

|=user=|=week=|=points=|
| 1    | 1    | 10     |
| 1    | 2    | 14     |
| 3    | 1    | 1      |
| 2    | 3    | 4      |
| 4    | 4    | 1      |
| 3    | 1    | 1      |

and then with each week, every week for everyone must decrease by one point, so the following week it would run a query

UPDATE `userpoints` SET `points = (`points`-1)

resulting in:

|=user=|=week=|=points=|
| 1    | 1    | 9      |
| 1    | 2    | 13     |
| 3    | 1    | 0      |
| 2    | 3    | 3      |
| 4    | 4    | 0      |
| 3    | 1    | 0      |

it obviusly works, but then I have all those useless entries with zero points, so I have to run a delete after.

DELETE FROM `userpoints` WHERE `points` <= 0

so, would it be possible to run a query that would update the table, and also delete the unwanted entries in the process?

leatherback

Hi,

I am not sure, but don't think so.
What is the problem with running two queries though?

Oh, and delete before update reduces db query effort

Jack_McSlay

the problem with running two queries? none. I'm asking this for learning purposes

jheitz

A couple of ways to do this more efficiently:

1) If you are using SQL Servefr you could use an UPDATE trigger on your table. When the points field is updated, if the value is <= 0 then delete the record

2) Any database system that supports Stored Procedures. Call a Stored Procedure that performs both the update and the delete statement. This way you are only calling one database process from within PHP, which is much better

thorpe

Yeah, if your using MySql 5 or later, setting up a trigger to fire off on the tables UPDATE would be the way to go.