CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Using WHERE clauses in an UPDATE statement

I have an update statement that's like this:

update user_stats set
    requestsRecd = (select count(*) from requests where requestedUserId = 1) where userId = 1,
    requestsSent = (select count(*) from requests where requesterUserId = 2) where userId = 2;

What I'm trying to do, is update the same table, but different users in that table, with the count of friend requests received for one user, and the count of friend requests sent by another user.

What I'm doing works if I remove the where clauses, but then, that updates all the users in the entire table.

Any idea how I can do something like this with the where clauses in there or achieve the same results using another approach?

Upvotes: 1

Views: 245

Answers (3)

spencer7593
spencer7593

Reputation: 108510

(As proposed in several other answers, obviously, you could run two separate statements, but to answer the question you asked, whether it was possible, and how to do it...)

Yes, it is possible to accomplish the update operation with a single statement. You'd need conditional tests as part of the statement (like the conditions in the WHERE clauses of your example, but those conditions can't go into a WHERE clause of the UPDATE statement.

The big restriction we have with doing this in one UPDATE statement is that the statement has to assign a value to both of the columns, for both rows.

One "trick" we can make use of is assigning the current value of the column back to the column, e.g.

 UPDATE mytable SET mycol = mycol WHERE ...

Which results in no change to what's stored in the column. (That would still fire BEFORE/AFTER update trigger on the rows that satisfy the WHERE clause, but the value currently stored in the column will not be changed.)

So, we can't conditionally specify which columns are to be updated on which rows, but we can include a condition in the expression that we're assigning to the column. As an example, consider:

 UPDATE mytable SET mycol = IF(foo=1, 'bar', mycol)

For rows where foo=1 evaluates to TRUE, we'll assign 'bar' to the column. For all other rows, the value of the column will remain unchanged.

In your case, you want to assign a "new" value to a column if a particular condition is true, and otherwise leave it unchanged.

Consider the result of this statement:

UPDATE user_stats t
  SET t.requestsRecd = IF(t.userId=1, expr1, t.reqestsRecd) 
    , t.requestsSent = IF(t.userId=2, expr2, t.reqestsSent) 
WHERE t.userId IN (1,2);

(I've omitted the subqueries that return the count values you want to assign, and replaced that with the "expr1" and "expr2" placeholders. This just makes it easier to see the pattern, without cluttering it up with more syntax, that hides the pattern.)

You can replace expr1 and expr2 in the statement above with your original subqueries that return the counts.

As an alternative form, it's also possible to return those counts on a single row, using in an inline view (aliased as v here), and then specify a join operation. Something like this:

UPDATE user_stats t
 CROSS
  JOIN ( SELECT (select count(*) from requests where requestedUserId = 1) AS c1
              , (select count(*) from requests where requesterUserId = 2) AS c2
       ) v
   SET t.requestsRecd = IF(t.userId=1, v.c1 ,t.reqestsRecd) 
     , t.requestsSent = IF(t.userId=2, v.c2 ,t.reqestsSent)
 WHERE t.userId IN (1,2)

Since the inline view returns a single row, we don't need any ON clause or predicates in the WHERE clause. (*I typically include the CROSS keyword here, but it could be omitted without affecting the statement. My primary rationale for including the CROSS keyword is to make the intent clear to a future reader, who might be confused by the omission of join predicates, expecting to find some in the ON or WHERE clause. The CROSS keyword alerts the reader that the omission of join predicates was intended.)

Also note that the statement would work the same even if we omitted the predicates in the WHERE clause, we could spin through all the rows in the entire table, and only the rows with userId=1 or userId=2 would be affected. (But we want to include the WHERE clause, for improved performance; there's no reason for us to obtain locks on rows that we don't want to modify.)

So, to summarize: yes, it is possible to perform the sort of conditional update of two (or more) rows within a single statement. As to whether you want to use this form, or use two separate statements, that's up for you to decide.

Upvotes: 2

Juru
Juru

Reputation: 1629

You are trying to update two different rows at the same time. That is not possible. Use two update queries then.

update user_stats set
requestsRecd = (select count(*) from requests where requestedUserId = 1) where userId = 1;

and

update user_stats set
requestsSent = (select count(*) from requests where requesterUserId = 2) where userId = 2;

Tell me if that works or not.

Upvotes: 0

Jim
Jim

Reputation: 22656

What you're trying to do is two updates try splitting these out:

update user_stats set
    requestsRecd = (select count(*) from requests where requestedUserId = 1) where userId = 1;
update user_stats set
    requestsSent = (select count(*) from requests where requesterUserId = 2) where userId = 2;

There may be a way using CASE statements to dynamically chose a column but I'm not sure if that's possible.

Upvotes: 1

Related Questions