skip to Main Content

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

2

Answers


  1. 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. 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