0

I am trying to do an updated statement based on a select that uses a HAVING clause and calculated field in MySQL. My Select is:

SELECT FLOOR( IF( file_real_id, file_real_id, file_id ) /5000 ) AS fldrid, file_id
FROM Files
WHERE srv_id =2
HAVING fldrid =0

Now I want to update all the records identified with the file_id above and set their srv_id to 3. Essentially

UPDATE Files SET srv_id = 3 
[for all records identified with their file_id in the above SELECT]

I tried all the answers proposed in Using a HAVING clause in an UPDATE statement but I cannot get any of them to work without MySQL errors.

Per Comment Below, Here was the attempt using inner join.

update f1 set
srv_id=3
from
Files f1 
inner join (
    SELECT file_id,
    FROM Files
    WHERE srv_id =2
    HAVING FLOOR( IF( file_real_id, file_real_id, file_id ) /5000 )=1
    ) f2 on
f1.file_id = f2.file_id;

This was following the suggestion in the link earlier but has syntax errors. The other was:

UPDATE Files SET Srv_id = 3 Where file_id IN (
SELECT file_id,
FROM Files
WHERE srv_id =2
HAVING FLOOR( IF( file_real_id, file_real_id, file_id ) /5000 )=1))
Community
  • 1
  • 1
OldTimerRestarting
  • 1
  • 1
  • 2

1 Answers1

4

Try either

UPDATE files f JOIN
(
  SELECT file_id,
    FROM files
   WHERE srv_id =2
  HAVING FLOOR(IF(file_real_id, file_real_id, file_id) / 5000) = 1
) q ON f.file_id = q.file_id
   SET srv_id = 3;

or

UPDATE files 
   SET srv_id = 3 
 WHERE file_id IN 
(
  SELECT file_id
    FROM
  (
    SELECT file_id,
      FROM files
     WHERE srv_id =2
    HAVING FLOOR(IF(file_real_id, file_real_id, file_id) / 5000) = 1
  ) q
);
peterm
  • 91,357
  • 15
  • 148
  • 157