Using UPDATE to update only 500 Rows

August 21, 2024

I have a table with close to 10000 rows I want to update the first 500 rows using SQL

UPDATE gs_user_objects_copy SET user_id = '269' WHERE user_id = '14' LIMIT 500

I want to change the user_id to 269 where it was 14 but only the first 500 found in the Database. It looks like the LIMIT function is erroring out

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(500) gs_user_objects_copy WHERE user_id = ’14” at line 1

Out of the database table the first 500 entries should change to the new value

>Solution :

The problem you are having is LIMIT can’t be used, instead use subquery


UPDATE gs_user_objects_copy 
JOIN (
    SELECT id FROM gs_user_objects_copy 
    WHERE user_id = '14'
    ORDER BY id
    LIMIT 500
) AS subquery ON gs_user_objects_copy.id = subquery.id
SET gs_user_objects_copy.user_id = '269';

Alternatively

If you want to use CTE which is works just fine in this case , here is a snippet of how it is done

WITH cte AS (
    SELECT id
    FROM gs_user_objects_copy
    WHERE user_id = '14'
    ORDER BY id
    LIMIT 500
)
UPDATE gs_user_objects_copy
JOIN cte ON gs_user_objects_copy.id = cte.id
SET gs_user_objects_copy.user_id = '269';