CODEWITHSUNDEEP
mysqlsqlsql-order-byinner-joinlimit
djd
djd

Reputation: 87

MySQL Update using INNER JOIN with ORDER BY and LIMIT

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?

Upvotes: 1

Views: 4881

Answers (3)

Saquib Azam
Saquib Azam

Reputation: 83

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'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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

Upvotes: 3

Related Questions