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?

Answer 1

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.