SQL: update a row if condition on another row

Question

I have a problem in SQL.

I want to update a value on a given row if and only if a value on another row matches a condition; then update another value on the second row.

Ok it can't be clear if I explain it in this way, so here's the code (I'm gonna bind the parameters using mysqli):

--mariaDB:

UPDATE `accountlist` JOIN `data` ON `accountlist`.`id`=`data`.`id`
    SET `upvotes`= `upvotes` + (`user`= ?),
        `allow` = (CASE WHEN `accountlist`.`id` = ?
                   THEN ?
                   ELSE `allow`
                   END)
WHERE (SELECT `allow` FROM `data` WHERE `id` = ?) < ?;

--mysql:

UPDATE `accountlist` JOIN `data` ON `accountlist`.`id`=`data`.`id`
    SET `upvotes`= `upvotes` + (`user`= ?),
        `allow` = (CASE WHEN `accountlist`.`id` = ?
                   THEN ?
                   ELSE `allow`
                   END)
WHERE `data`.`id` = ? AND `allow` < ?;

--params sample: "admin", 2, "2020-04-20", 2, "2020-04-20"
--               (same value here means always same value)

I have to keep both MySQL and non-MySQL versions because I have different databases on localhost and my host, and for some reason the first version does not work with MySQL.

data is another table which has a one-to-one relationship with accountlist (they always have the same number of rows with the same ids)

Anyway, I'm gonna call the row where user=? row 1 and the row where accountlist.id=? row 2 to simplify everything.

What I want do do is:

update user on row 1 if allow on row 2 is less than ?
update allow on row 2 (if allow on row 2 is less than ?)

The condition on the second point doesn't really matter because it would update to itself, but that's the only way I was able to do that.

My problem:

The first piece of code (non-MySQL) updates upvotes on row 1 but never changes allow on row 2.
The second piece of code (MySQL) updates arrow on row 2 but never changes user on row 1.

Do you have any solutions, which could maybe imply an unique MySQL + non-MySQL version?

UPDATE:

Here an example:

     accountlist                       data
   |------------|---------------|    |------------|--------------|----------|
   | id         | user          |    | id         | allow        | upvotes  |
   |------------|---------------|    |------------|--------------|----------|
A: | 1          | admin         |    | 1          | 2020-04-18   | 2        |
B: | 2          | foo           |    | 2          | 2020-04-20   | 0        |
C: | 3          | bar           |    | 3          | 2020-04-22   | 1        |
   |------------|---------------|    |------------|--------------|----------|

params: "admin", 2, "2020-04-20", 2, "2020-04-20" allow on row B is not lower than 2020-04-20:

nothing happens.

params: "admin", 2, "2020-04-22", 2, "2020-04-22" allow on row B is lower than 2020-04-20:

upvotes on row A in increased (2->3)
allow on row B is updated (2020-04-20->2020-04-22)

params: "bar", 1, "2020-04-19", 1, "2020-04-19" allow on row A is lower than 2020-04-19:

upvotes on row C is increased (1->2)
allow on row A is updated (2020-04-18->2020-04-19)

UPDATE 2:

What I want to do: If user 1 wants to upvote user 2 (everyone can upvote someone else at most once a day), when it clicks a button, allowed (on his row) is compared with the day after:

if allowed is equal to the day after, it means that user 1 has already upvoted someone (he could be user 2, user 3 or someone else), so nothing changes
if allowed is lower than the day after, it means that user 1 is allowed to upvote someone, so upvote on user 2's row is incremented and allow on user 1's row is updated to the day after

Don't worry about "what if user 1 or user 2 doesn't actually exist?", or "what if user 1 tries to upvote himself?* because I check about it in my PHP code.

This is probably doable, but your question is quite unclear as it stands. Please provide sample data and expected results, as tabular text to clarify what you actually want (usually, 5 to 10 rows are enough). — GMB, Apr 23 '20 at 13:19
How is "row 1" defined? Based on `id`? Based on "next" in some order, if so, what ordering? — Rick James, Apr 23 '20 at 14:10
Why can't you just use the second version for both databases? — sloppypasta, Apr 23 '20 at 14:26
@RickJames if you mean "what's the primary key?", it is `id` (but I can't see why should it matter). If you mean "why did you call those rows *row 1* and *row 2*?", it was just to refer to them somehow (I could even call them *row A* and *row B*, just to underline the fact that they are separate rows) — , Apr 23 '20 at 14:35
@works_as_coded as far as I know you can't use `WHERE x=? AND y=?;` to select a row in a database different from *MySQL* (the opposite should work, but for some reason it doesn't) — , Apr 23 '20 at 14:38
@Foxel I'm not sure why you think there is something special about MySQL. Give it a try. — sloppypasta, Apr 23 '20 at 15:18
@works_as_coded I read about it on another answer here on Stack Overflow, but at the moment I cannot find it, sorry... — , Apr 23 '20 at 15:50
@works_as_coded anyway, a solution which works in both databases would be much better: the problem is that at the moment none of them work (see "My problem") inside the question — , Apr 23 '20 at 15:51
Why don't you do something like "check if user is allowed to vote, increase vote, update date"?. Anyway, your mariadb code should work. Not fast or beautiful or for all edge cases (e.g. it doesn't check if row 1 exists), but it shouldn't have the problem you describe - unless there is no matching entry for row 1 or 2 in `data`. Please check that and clarify if `data` is relevant, you never mention it. It's trivial to make this mariadb query work in mysql ("I do this as I get an error in MySQL" explains your reasons better than "I read about it"), but you should clarify your requirements first. — Solarflare, Apr 25 '20 at 09:06
@Solarflare 1-`data` is a one-to-one relationship table which I created just because `accountlist` was going to have far too many columns (soo I just moved the less relevant ones). 2- I check for the matching entry for *row 1* and *row 2* inside my PHP code using `$stmt->affected_rows`: if it returns 0 I know that *row 1/2* does not exist or *row 2* does not satisfy the requirements (and that's enough for my purpose). 3- The problem is that at the moment is does not work even in MariaDB... Could it have something to do with the fact that the values are on two different tables? — , Apr 25 '20 at 09:37
@Solarflare check my updated answer for a better explaination of my requirements list — , Apr 25 '20 at 09:58
I made you a fiddle with your sample data, your parameters and your mariadb query, see [here](https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=05f809fecbf606c98e03194fa9a3d4ac). As far as I can see it does what you describe. Please make me a fiddle/edit that fiddle so that it fits your problem description that it "upvotes on row 1 but never changes allow on row 2." — Solarflare, Apr 25 '20 at 10:26
And to reiterate my first sentence from my first comment: since you now say you actually do checks before you start the update to make sure some conditions are met (user exists, ...): why don't you just add the last requirement of having the right to vote into that logic too? That's *a lot* less complicated (e.g. if someone else (or you in a year from now) have to understand it) and *a lot* easier to maintain (e.g. if you have a 3rd thing to update, or another condition, or whatever) and would probably even be faster (and lock less rows). — Solarflare, Apr 25 '20 at 10:31
@Solarflare it works on MariaDB! (you switched `data` and `accountlist` but everything else was correct) My error was that on the last line I selected `allow` where `accountlist.id=?` instead of `data.id=?`. The problem is that now on *MySQL* it thows the following error using that same code: `You can't specify target table 'accountlist' for update in FROM clause`. — , Apr 25 '20 at 11:03
@Solarflare I could use PHP to check if the user is allowed to upvotes, but this would require two different queries (which slow down everything) because `allow` is stored on the database, while I can check everythinng else using session variables that I already have on the backend — , Apr 25 '20 at 11:05
Using a complicated solution just because you *assume* two queries would be slower than 1 is not always the best idea. Your current solution is actually slower than 2 separate queries (although for 3 users, you cannot measure that). But it's fine if you want to do that, it was just a remark. Your MariaDB->MySQL problem is actually a separate problem and should be a different question. It has actually been asked before (very often), see [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/q/45494). A trivial workaround is to nest the subquery. — Solarflare, Apr 25 '20 at 11:28
Ok I solved the problem in this way: `UPDATE accountlist JOIN...WHERE (SELECT allow FROM (SELECT * FROM data WHERE data.id = ?) AS TEMP) < ?;`. This is an ugly solution but it actually works for both MariaDB and MySQL. Give me some time to test it out deeper and then if you want to add an answer (perhaps with an optimized version of this MySQL query which I'm sure is not the best way to make it work) I can give you the bounty — , Apr 25 '20 at 12:06
@Solarflare see [this](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=6cb43a25b94d5a8ec76ab5f108e37987) — , Apr 25 '20 at 12:44

Rick James · Answer 1 · 2020-04-25T17:08:35.570

Let's walk through things. First, let be qualify each column to help see which column is in which table:

UPDATE a JOIN d ON a.`id` = d.`id`
    SET d.`upvotes`= d.`upvotes` + (a.`user`= ?),
        d.`allow` = (CASE WHEN a.`id` = ?
                   THEN ?
                   ELSE d.`allow`
                   END)
WHERE d.`id` = ? AND d.`allow` < ?;

Oh, the two tables are 1:1 on id. So let's pretend they are the same table. (And you should probably do that. 1:1 is usually not a good schema design.)

UPDATE ad
    SET `upvotes`= `upvotes` + (`user`= ?),
        `allow` = (CASE WHEN `id` = ?
                   THEN ?
                   ELSE `allow`
                   END)
WHERE `id` = ? AND `allow` < ?;

Now it is obvious that the CASE is wasted. So the query simplifies to

UPDATE ad
    SET `upvotes`= `upvotes` + (`user`= ?),
        `allow` = ?
WHERE `id` = ? AND `allow` < ?;

Now let's go back to having 2 tables:

UPDATE a JOIN d ON a.`id` = d.`id`
    SET d.upvotes = d.upvotes + (a.user = ?),
        d.allow = ?
    WHERE d.id = ? AND d.allow < ?;

Which leads to another form. (I don't know if this is better or not.)

UPDATE d
    SET d.upvotes = d.upvotes +
                    ( ( SELECT  user = ? FROM a WHERE a.id = d.id ) )
        d.allow = ?
    WHERE d.id = ? AND d.allow < ?;

Or, (again possibly not any significant difference):

UPDATE d
    SET d.upvotes = d.upvotes +
                EXISTS ( SELECT  user = ? FROM a WHERE a.id = d.id )
        d.allow = ?
    WHERE d.id = ? AND d.allow < ?;

It does not work: it correctly updates allow on what I call *row 2* (where `id=?`) but user remains the same in every single case (with all of your versions). See the comments between me and @Solarflare for more informations: we figured out [some ideas](https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=c811d618caf9f627f447c703ebcf62f5), but they all have some kinds of problems, from compatibility between *MariaDB* and *MySQL* to a version similar to yours which does not work in some cases. I also figured out something that works (see the link) but it seems a workaround more than a solution — , Apr 25 '20 at 19:43
@Foxel - The first 'UPDATE' does not show that `user` needs to change; please edit it accordingly. — Rick James, Apr 25 '20 at 23:30

score 0 · Answer 2 · answered Apr 29 '20 at 14:46

I have tried to find a solution for your problem and I have possibly an answer id i understand correctly here the wor flow : each user can upvote 1 time per day

user1 upvote user2
have user1 already upvote today ?
->yes : do nothing
->no : change date of user1 and +1 to upvotes of user2

in that exemple : "admin", 2, "2020-04-20", 2, "2020-04-20"

2 => user1
admin => user2
"2020-04-20" => date of upvote

Am I correct ? if yes here my solution

mariadb

UPDATE data SET upvotes = CASE WHEN id = (SELECT id FROM accountlist WHERE accountlist.user = ?)
                AND 
               (SELECT allow FROM data inner join accountlist on accountlist.id = data.id 
                 where accountlist.id = ? )< ?
            THEN upvotes + 1
            ELSE upvotes
          END,
allow = CASE WHEN id = ?
THEN ?
ELSE allow
END

mysql

UPDATE data
SET upvotes = CASE WHEN id = (SELECT id FROM (select * from accountlist) as al WHERE al.user = "bar")
                    AND 
                   (SELECT allow FROM (select * from data) as d inner join (select * from accountlist) as al1 on al1.id = d.id where al1.id = 1)<"2020-04-19"
                THEN upvotes + 1
                ELSE upvotes
              END,
allow = CASE WHEN id = 1
THEN "2020-04-19"
ELSE allow
END;

created database :

         accountlist                       data
   |------------|---------------|    |------------|--------------|----------|
   | id         | user          |    | id         | allow        | upvotes  |
   |------------|---------------|    |------------|--------------|----------|
A: | 1          | admin         |    | 1          | 2020-04-18   | 2        |
B: | 2          | foo           |    | 2          | 2020-04-20   | 0        |
C: | 3          | bar           |    | 3          | 2020-04-22   | 1        |
   |------------|---------------|    |------------|--------------|----------|

with value : "admin", "2", "2020-04-20", "2", "2020-04-20" => no modification

"admin", "2", "2020-04-22", "2", "2020-04-22"

         accountlist                       data
   |------------|---------------|    |------------|--------------|----------|
   | id         | user          |    | id         | allow        | upvotes  |
   |------------|---------------|    |------------|--------------|----------|
A: | 1          | admin         |    | 1          | 2020-04-18   | 3        |
B: | 2          | foo           |    | 2          | 2020-04-22   | 0        |
C: | 3          | bar           |    | 3          | 2020-04-22   | 1        |
   |------------|---------------|    |------------|--------------|----------|

"bar", 1, "2020-04-19", 1, "2020-04-19" =>

             accountlist                       data
   |------------|---------------|    |------------|--------------|----------|
   | id         | user          |    | id         | allow        | upvotes  |
   |------------|---------------|    |------------|--------------|----------|
A: | 1          | admin         |    | 1          | 2020-04-19   | 3        |
B: | 2          | foo           |    | 2          | 2020-04-22   | 0        |
C: | 3          | bar           |    | 3          | 2020-04-22   | 2        |
   |------------|---------------|    |------------|--------------|----------|

normally that run correctly (tested online) but that coul have been simplier to use 2 statement instead of 1.

It works on MariaDB but it doesn't on MySQL. The strange fact is that I tried it on a [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=05145d6d0ee92daeedbc82174803030d) and it seems to work even with MySQL (it is a little modified but same result with [your one](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8f5587a28a71d4673e9ed066a667e58d) . Any suggestions? — , Apr 29 '20 at 21:36
On your fiddle, when you change sql version from 8.0 to 5.7 the request fail. which version is used by you server ? — Gautier Logeot, Apr 30 '20 at 11:41
I have tried on [link](https://www.db-fiddle.com) and everything work even in 5.7. no error phpmyadmin ? — Gautier Logeot, Apr 30 '20 at 12:52
Your link is broken. Anyway, I get no error: it correctly updates `allow` but `upvotes` where `user=?` always remains the same — , Apr 30 '20 at 14:43
don't have a clue sorry, I presume all condition for update are ok ? — Gautier Logeot, Apr 30 '20 at 15:08
Even replacing `(SELECT id FROM (select * from accountlist) as al WHERE al.user = "bar")` with the exact id number it does not work, while if I remove that condition from the query it updates everything, so something goes wring with the compare between `id` and the first subquery — , Apr 30 '20 at 18:13
in fact `(select * from accountlist)` was something I found online because I could not use the same table in principal and sub query. so maybe a special issue with that — Gautier Logeot, May 04 '20 at 08:21
The only way it works is by joining the two tables after "update", but that slows down the query quite a lot — , May 04 '20 at 09:28
could it not be possible to do all this with two separate query ? — Gautier Logeot, May 04 '20 at 11:22
Yes. At this point I think I will do some benchmark between the unique query with `JOIN` and the two separate queries and choose the fastest one. Thank you for everything — , May 04 '20 at 11:27

SQL: update a row if *condition* on another row

2 Answers2

SQL: update a row if condition on another row