Question 241254 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Updating row withing select depending on result of union select

I have a table called "scores" with following info:

| id | name  | datetime_added      | option |
+----+-------+---------------------+--------+
| 1  | all   | 2023-01-01 00:10:04 | none   |
+----+-------+---------------------+--------+
| 2  | James | 2023-01-01 00:32:18 | none   |
+----+-------+---------------------+--------+
| 3  | all   | 2023-01-01 02:08:51 | none   |
+----+-------+---------------------+--------+
| 4  | all   | 2023-01-01 04:13:07 | none   |
+----+-------+---------------------+--------+
| 5  | Rubio | 2023-01-01 07:05:55 | none   |
+----+-------+---------------------+--------+
| 6  | all   | 2023-01-01 09:17:27 | none   |
+----+-------+---------------------+--------+
| 7  | James | 2023-01-01 10:43:01 | none   |
+----+-------+---------------------+--------+

For output I need to add option "some" to the newest entry of "Rubio" where option is "none" if there is any. If there isn’t (either name "Rubio" or option "none") then I need to add the option "some" to the newest entry of "all".

What I have done so far? I need to get that table sorted in two ways:

  1. By name, "Rubio" needs to be first and "all" needs to be second (names can change and there can be more of them, so alphabetical is not helpful here but 1 name out of all needs to come first, then all and rest ignored).
  2. By datetime_added (ID is auto increment so sorting by id should do the trick)

I can manage this by doing:

(SELECT * FROM scores WHERE name = "Rubio" AND option = "none" ORDER BY id DESC)
UNION
(SELECT * FROM scores WHERE name = "all" AND option = "none" ORDER BY id DESC);

This giving me necessary list of:

| id | name  | datetime_added      | option |
+----+-------+---------------------+--------+
| 5  | Rubio | 2023-01-01 07:05:55 | none   |
+----+-------+---------------------+--------+
| 1  | all   | 2023-01-01 00:10:04 | none   |
+----+-------+---------------------+--------+
| 3  | all   | 2023-01-01 02:08:51 | none   |
+----+-------+---------------------+--------+
| 4  | all   | 2023-01-01 04:13:07 | none   |
+----+-------+---------------------+--------+
| 6  | all   | 2023-01-01 09:17:27 | none   |
+----+-------+---------------------+--------+

How could I update the table within this one query now when I know I need to:

UPDATE scores SET option = "some" WHERE id = 5;

This request is also run up to once per second, is this efficient?

Tags:

2

Answers


  1. 0

    Don’t use UNION, use LEFT JOIN. Then you can use IFNULL() to provide one selection as a default if the other selection isn’t found.

    UPDATE scores AS s
JOIN (
  SELECT IFNULL(t1.id, t2.id) AS id
  FROM (SELECT id
        FROM scores
        WHERE name = 'Rubio' AND `option` = 'none'
        ORDER BY id DESC
        LIMIT 1) AS t1
  LEFT JOIN (SELECT id
        FROM scores
        WHERE name = 'all' AND `option` = 'none'
        ORDER BY id DESC
        LIMIT 1) AS t2 ON 1 = 1
) AS x ON s.id = x.id
SET s.option = 'some'

    DEMO

    Login or Signup to reply.
  2. 0

    For output I need to add option "some" to the newest entry of "Rubio"
    where option is "none" if there is any. If there isn’t (either name
    "Rubio" or option "none") then I need to add the option "some" to the
    newest entry of "all".

    This can be done by a case statement in the order by clause ,

     order by case 
            when name='Rubio' and `option`='none' then 1 
            when name ='all' and `option`='none' then 2 
            else 3 end  asc, datetime_added desc ) row_num

    Use row_number window function to give update the value

    WITH ToUpdate AS (
   select id,
          row_number() over( order by case when name='Rubio' and `option`='none' then 1 
                                           when name ='all' and `option`='none' then 2 
                                           else 3 end  asc,datetime_added desc ) row_num
  from scores
  where `option`='none' 
  and name in ('all','Rubio')
)   
UPDATE scores  
INNER JOIN  ToUpdate  ON scores.id = ToUpdate.id
SET  `option` = 'some' 
WHERE ToUpdate.row_num = 1;

    Above query will update only if name='Rubio' and option='none' or name ='all' and option='none' otherwise would do nothing because other records are filtered in the where condition where option='none' and name in ('all','Rubio')

    See example

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

Back To Top
Search