Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Using UPDATE to update only 500 Rows

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

Tags:

2

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    The LIMIT can not be directly supported in standard SQL including mysql. You can use subquery to update the first 500 rows.

    UPDATE gs_user_objects_copy 
SET user_id = '269' 
WHERE id IN (
    SELECT id 
    FROM (
        SELECT id 
        FROM gs_user_objects_copy 
        WHERE user_id = '14' 
        ORDER BY id 
        LIMIT 500
    ) AS subquery
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search