7

Can i use LIMIT 2 on MySQL INSERT query? e.g.

INSERT INTO MyTable
   (user_name,password)
VALUES
   (john,366543),
   (sam,654654)
LIMIT 2

I tried and its saying 

`#1064 - 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 'LIMIT 2' at line 1`
John Smith
  • 681
  • 5
  • 13
  • 30

7 Answers7

9

You could do this using the INSERT ... SELECT syntax:

INSERT INTO MyTable (user_name, password)
SELECT 'john', '366543'
UNION ALL SELECT 'sam', '654654'
LIMIT 2;

Not sure why you would want to. Maybe if you had a very long list of static values that you wanted to easily control by setting the limit?

As pst noted in a now deleted comment, the LIMIT is actually part of the SELECT, and has nothing to do with the INSERT itself.

yivi
  • 42,438
  • 18
  • 116
  • 138
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • 1
    You'd think it'd be obvious, right? If you want to limit the amount of data you're inserting, don't supply as many values. `LIMIT` in an `INSERT INTO ... SELECT FROM` call is relevant as demonstrated here. – tadman Aug 01 '12 at 22:15
  • LIMIT can be used with UPDATE and DELETE too. So it's not part of SELECT only. – Andrew Jun 22 '14 at 08:09
1

Mostly If we are inserting data from another table that time we need to set limit to inserting specific numbers of data 

     insert into cas_user (cas_name) select cas_name from users limit 1,5;

Hope this will help.

Ajay Gadhavana
  • 415
  • 5
  • 5
1

If you are trying to insert huge bulk data in parts by using limit, you are operating within the initial constraints laid down by the MySQL.

Try increasing the values of the constraints rather : PFB

Variables : max_allowed_packet, bulk_insert_buffer_size, key_buffer_size

Sample queries to show and set :

show variables like 'max_allowed_packet';
SET GLOBAL max_allowed_packet=524288000;

References:
http://forums.mysql.com/read.php?20,161869

MySQL - how many rows can I insert in one single INSERT statement?

Community
  • 1
  • 1
Vivek Vardhan
  • 41
  • 4
0

LIMIT 2 will only work with select no values

Ruzbeh Irani
  • 2,318
  • 18
  • 10
0

If you want to partially copy an existing table, you could do the following (I needed this to run some tests on that table).

CREATE TABLE copy SELECT * FROM source_table LIMIT 1000;

You can of cause use a temporary table to copy also from one table into another.

CREATE TEMPORARY TABLE copy SELECT * FROM source_table LIMIT 1000;
INSERT INTO target_table SELECT * FROM copy;
DROP TABLE copy;
crash
  • 603
  • 4
  • 11
-1

I know it's an old post but you can use foreach loop to limit insert statement. Something like:

    $i = '1';   
    foreach($item as $item){
        IF($i <= '2'){
            IF ($stmt = $connection->prepare("INSERT IGNORE INTO `db`.`table`( `item`) VALUES (?)")) {

                /* Bind our params */
                $stmt->bind_param('s' , $item);

                $stmt->execute();

                $stmt->close();
            }
        }
        $i++;
    }
Robert
  • 1
-3

no you cannot use limit in insert query

Selva
  • 1