Mysql – updating n number of records thesql database

limitsMySQLmysql-5.7update

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key

update producedItem pi 
inner join productionOrderLine pol on pol.id = pi.productionOrderLineId
set pi.isReserved = 1
limit floor(pol.quantity - pol.availableQuantity);

what would be the better alternative?

Best Answer

Write a Stored Procedure that 'constructs' the query, specifically by putting a computed value after LIMIT.

Related Solutions

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

We discovered the solution thanks to a DBA who took a look. I wanted to leave the answer to help anyone else who may be in a similar situation.

The key was that the tables had different collations, but we were joining the tables on a varchar field. So, MySQL had to perform an implicit re-collation before it was able to join the tables on our join condition. Thus, it couldn't use any indexes.

So, to anyone else who comes across this: check your table collation if you are joining across varchar fields!

Limiting the Number of Records Joined

In SQL Server and other systems that support ROW_NUMBER()...

WITH GoalsWithGoalNum (
    SELECT *, 
        ROW_NUMBER() OVER (
            PARTITION BY [In] 
            ORDER BY [Goal ID]
        ) AS GoalNum
    FROM Goals
)
SELECT *
FROM GoalsWithGoalNum
WHERE GoalNum <= 2;

Best Answer

Related Solutions

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Limiting the Number of Records Joined

Related Question