Using limit with update in mysql

Question

I am trying to use limit with update in mysql but is giving an error

Error: MySQL Error: Incorrect usage of UPDATE and LIMIT

update table1
JOIN table2 
  ON SUBSTRING_INDEX(table1.HOST, '.',1)= SUBSTRING_INDEX(table2.HOST, '.', 1)
set table1.portalId = table2.portalId
limit 2;

Well, the documentation clearly states that "For multiple-table syntax, ORDER BY and LIMIT cannot be used." (https://dev.mysql.com/doc/refman/8.0/en/update.html) — Mureinik, Jun 28 '18 at 20:38

score 0 · Answer 1 · answered Jun 28 '18 at 20:38

0

However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE.

From the official reference

It's also the exact text shown when you search google for "mysql update limit".

answered Jun 28 '18 at 20:38

Uueerdo

15,723
1
16
21

score 0 · Answer 2 · answered Jun 28 '18 at 20:43

0

LIMIT can be used with UPDATE but with the row count only. You can try doing an inner select which may be more complicated but you may just be able to use your desired limit in there

MYSQL Reference

answered Jun 28 '18 at 20:43

BlackMarker

139
12

Using limit with update in mysql

2 Answers2