MySQL Update using INNER JOIN with ORDER BY and LIMIT

Question

I'm trying to do an update using an inner join with limit and order by (although the order by is not essential. From what I have read up the standard update will not work... this is what I am trying to do:

UPDATE table1
INNER JOIN table2
ON table1.service_id=table2.service_id
SET table1.flags = NULL
WHERE table1.type = 'fttc'
AND table1.flags = 'co'
AND table2.sync not like '%Yes%'
AND table1.date >= $today_date
ORDER BY table1.priority ASC
LIMIT 20;

it is for use in a case management tool and using php, I want to update 20 tickets i.e. remove the 'flag' so that they can be worked, the quantity will be passed as a variable, so I want to update 20 tickets for example highest 'priority' first, if that can be done?

If the order by is not important then limit is neither, right? — juergen d, Apr 17 '17 at 09:52
Order by in an update is meaningless. Please clarify what you want to accomplish. Why use the limit? — Sloan Thrasher, Apr 17 '17 at 09:56
it is for use in a case management tool and using php, I want to update 20 tickets i.e. remove the 'flag' so that they can be worked, the quantity will be passed as a variable, so I want to update 20 tickets for example highest 'priority' first, if that can be done? — djd, Apr 17 '17 at 10:00

score 3 · Answer 1 · answered Apr 17 '17 at 10:04

If I read your question correctly, you want to perform an update on the first 20 records which results from the join, using the priority as ordering. You cannot do this directly in an UPDATE in MySQL AFAIK, but you can create an updatable view and then update that.

CREATE VIEW yourView
AS
SELECT
    t1.service_id,
    t2.service_id,
    t1.flags,
    t1.type,
    t1.date,
    t1.priority,
    t2.sync
FROM table1 t1
INNER JOIN table2 t2
    ON t1.service_id = t2.service_id
WHERE t1.type = 'fttc'         AND
      t1.flags = 'co'          AND
      t2.sync NOT LIKE '%Yes%' AND
      t1.date >= $today_date
ORDER BY t1.priority
LIMIT 20;

And then update this view:

UPDATE yourView
SET flags = NULL

I never thought about doing it this way, this will work, thank you. I knew it couldn't be done via standard update - this was an alternative I was looking for :-) — djd, Apr 17 '17 at 10:12
This is sort of MySQL's analogy to updating a CTE in another database like SQL Server. Keep it in your bag of tricks for future reference. — Tim Biegeleisen, Apr 17 '17 at 10:13

score 2 · Accepted Answer · answered Apr 17 '17 at 10:46

2

There should be no reason to use a view:

UPDATE table1 t1
    SET t1.flags = NULL
    WHERE t1.type = 'fttc' AND
          t1.flags = 'co' AND
          t1.date >= $today_date AND
          EXISTS (SELECT 1
                  FROM table2 t2
                  WHERE t2.service_id = t1.service_id AND
                        t2.sync not like '%Yes%'
                 )
    ORDER BY t1.priority ASC
    LIMIT 20;

You cannot use ORDER BY and LIMIT with a multiple table JOIN. However, you can move the condition on table2 to the WHERE clause.

answered Apr 17 '17 at 10:46

Gordon Linoff

1,242,037
58
646
786

just about to test this way of doing this, can you confirm what the value '1' represents at the SELECT 1 FROM table2 ? Thanks – djd Apr 17 '17 at 15:44
This does exactly what I need. Thank you. – djd Apr 17 '17 at 15:52
@djd . . . `EXISTS` checks if any *rows* are returned. The `1` is an arbitrary value that is easy to type. – Gordon Linoff Apr 17 '17 at 23:20
@GordonLinoff Then is my answer invalid? If so, I can remove it. – Tim Biegeleisen Apr 18 '17 at 00:30
@TimBiegeleisen . . . If your answer works, then it is valid. I just prefer to do this in one statement. – Gordon Linoff Apr 18 '17 at 00:46
We can use limit in nested select query and then use it in update like https://stackoverflow.com/a/45944431/2853332 – hmjha Aug 29 '17 at 16:51

score 0 · Answer 3 · edited Jun 23 '20 at 14:34

0

Following work for me:

UPDATE child AS upd
 JOIN (SELECT t1.id FROM child AS t1
 INNER JOIN master AS t2
 ON t2.id = t1.id
   where 1
   AND t2.`date` BETWEEN '2020-06-23 00:00:00' AND '2020-06-23 23:59:59' 
   AND t2.client_id= 10 AND t1.code NOT IN('11','22')
 order by t1.id desc LIMIT 1) AS col
 ON upd.id=col.id 
 SET upd.code= '33', upd.`resp` = 'done',upd.status='success'

edited Jun 23 '20 at 14:34

Matt Ke

3,599
12
30
49

answered Jun 23 '20 at 13:50

Saquib Azam

73
1
4

MySQL Update using INNER JOIN with ORDER BY and LIMIT

3 Answers3