MySQL update the same table based on results of a select

Question

I have a table called downloads which has records of files which have been downloaded from various URL's. There is a column downloads.created which is a DATETIME and a column downloads.master which is a boolean field to say which record is the master copy.

The following query successfully gets the records which have the most recent date:

SELECT t1.master
FROM downloads t1
WHERE t1.id = (SELECT t2.id
             FROM downloads t2
             WHERE t2.url_id = t1.url_id            
             ORDER BY t2.created DESC
             LIMIT 1)

I want to update these records by setting master to 1.

Both the SELECT and UPDATE I want to perform apply to the same table, downloads

I have tried the following MySQL - UPDATE query based on SELECT Query :

UPDATE downloads 
(
    SELECT t1.master
    FROM downloads t1
    WHERE t1.id = (SELECT t2.id
             FROM downloads t2
             WHERE t2.url_id = t1.url_id            
             ORDER BY t2.created DESC
             LIMIT 1)
 )
 SET master = 1

But this gives an error:

Error : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '( SELECT t1.master FROM downloads t1 WHERE t1.id = (SELECT t2.id ' at line 2

Other solutions I tried on the above link updated the entire table, where as I'm just trying to update the records retrieved from the working SELECT I have. Please can someone help point me in the right direction?

@PriyeshKumar The condition is to `SET master = 1` but I only want to do that for the records that are returned from the `SELECT` statement, not the entire table. — Andy, May 19 '17 at 09:00
The update statement applies to a table, not to the results of a select. Furthermore, you cannot select from a table that is being updated, so use joins as @Jens has suggested. — Shadow, May 19 '17 at 09:02
update t1.master SET t1.master = 1 where WHERE t1.id = (SELECT t2.id FROM downloads t2 WHERE t2.url_id = t1.url_id ORDER BY t2.created DESC LIMIT 1) — JYoThI, May 19 '17 at 09:03

JYoThI · Answer 1 · 2017-05-19T09:23:13.317

2

use join

update t1 SET t1.master = 1  from downloads as  t1 
join downloads as  t2 
on t2.url_id = t1.url_id 
ORDER BY t2.created DESC LIMIT 1

edited May 19 '17 at 09:23

answered May 19 '17 at 09:04

JYoThI

11,977
1
11
26

Gives an error `Error : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE t1.id = (SELECT t2.id FROM downloads t2 WHERE t2.url_id = t1.url_id ' at line 1` – Andy May 19 '17 at 09:05
For a start there's no such table as `master`. Re-read the original post - the table I am selecting from and updating is called `downloads`. The field I am trying to update is `downloads.master` – Andy May 19 '17 at 09:19
just change the master to downloads . try it now i updated my answer @Andy – JYoThI May 19 '17 at 09:23

score 1 · Accepted Answer · edited May 19 '17 at 10:05

You need to use join and update together to do something like this. Try

  UPDATE downloads d
  JOIN (SELECT t1.id FROM downloads t1 
        WHERE t1.id = (SELECT t2.id
                       FROM downloads t2
                       WHERE t2.url_id = t1.url_id            
                       ORDER BY t2.created DESC LIMIT 1
                       ) 
        ) a on d.id = a.id  SET d.`master` = 1

That should work, and maybe you could optimize little bit more how you extract most recent records

score 0 · Answer 3 · answered May 19 '17 at 08:59

0

try this

   UPDATE downloads 
    SET master = 1
    FROM
    (
        SELECT t1.master
        FROM downloads t1
        WHERE t1.id = (SELECT t2.id
                 FROM downloads t2
                 WHERE t2.url_id = t1.url_id            
                 ORDER BY t2.created DESC
                 LIMIT 1)
     )

answered May 19 '17 at 08:59

Prabhat G

2,974
1
22
31

Gives an error unfortuantely - `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM` – Andy May 19 '17 at 09:01

MySQL update the same table based on results of a select

3 Answers3