PHP Versions Question posted in
You can find a list of all versions here.

SQL: update a row if *condition* on another row – Php versions

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:

  1. update user on row 1 if allow on row 2 is less than ?
  2. 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.

Tags:

2

Answers


  1. 0

    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 < ?;
    Login or Signup to reply.
  2. 0

    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

    1. user1 upvote user2
    2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search