UPDATE with ORDER BY and LIMIT not working in MYSQL

by

Usually you can use LIMIT and ORDER in your UPDATE statements, but in your case not, as written in the MySQL Documentation 12.2.10. UPDATE Syntax:

For the multiple-table syntax, UPDATE updates rows in each table named
in table_references that satisfy the conditions. In this case, ORDER
BY and LIMIT cannot be used.

Try the following:

UPDATE Ratemaster
SET Ratemaster.Rate =
(
    SELECT Rates.Rate
    FROM Rates
    WHERE Ratemaster.user = Rates.user
    ORDER BY Rates.id
    LIMIT 1
)

More Related Contents:

  1. Why does MYSQL higher LIMIT offset slow the query down?
  2. Using variable in a LIMIT clause in MySQL
  3. Maximum number of records in a MySQL database table
  4. Combining UNION and LIMIT operations in MySQL query
  5. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  6. MySQL – UPDATE query with LIMIT
  7. How to set a maximum execution time for a mysql query?
  8. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  9. MySQL JOIN with LIMIT 1 on joined table
  10. MySql Query: Select top 3 rows from table for each category
  11. How to hack MySQL GROUP_CONCAT to fetch a limited number of rows?
  12. Mysql delete statement with limit
  13. MySQL LIMIT on DELETE statement
  14. How do you use MYSQL in ColdFusion? [closed]
  15. Using backticks around field names
  16. How do I do a bulk insert in mySQL using node.js
  17. Reorder / reset auto increment primary key
  18. Required to join 2 tables with their FKs in a 3rd table
  19. Save Data in Arabic in MySQL database
  20. Setting the MySQL root user password on OS X
  21. GROUP_CONCAT with limit
  22. SQL Query to show nearest date?
  23. How can I modify the size of column in a MySQL table?
  24. How to auto generate migrations with Sequelize CLI from Sequelize models?
  25. MySQL – Recursing a tree structure
  26. How do I delete all the duplicate records in a MySQL table without temp tables
  27. What are the differences between the BLOB and TEXT datatypes in MySQL?
  28. Encryption at rest and/or AES_ENCRYPT
  29. mysql create table dynamically
  30. Errno 121, duplicate key on write or update?

Leave a Comment