Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: Insert more than one row

    Is it possible to insert more than one row to a table with one query?

    Code:
    INSERT INTO TABLE SET ID=0;
    INSERT INTO TABLE SET ID=1;
    Code:
    INSERT INTO TABLE SET ID=(SELECT ID1 FROM TABEL1)
    - In this case answer form SELECT has more than one result so what to do?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please stop using the SET option of mysql's INSERT statement

    it's non-standard sql

    learn standard sql, it will help you in your career
    Code:
    INSERT
      INTO daTable
         ( column1
         , column2
         , column3 )
    VALUES
      ( 'curly'     , 'howard'  , '1903-10-22' )      
    , ( 'larry'     , 'fine'    , '1902-10-05' )      
    , ( 'moe'       , 'howard'  , '1897-06-19' )      
    , ( 'shemp'     , 'howard'  , '1895-03-17' )      
    , ( 'joe'       , 'besser'  , '1907-08-12' )      
    , ( 'curly joe' , 'de rita' , '1909-07-12' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Ok, thanks.
    Code:
    INSERT INTO TABLE SET (ID) SELECT ID1 FROM TABEL1
    But in this case there is no word 'VALUES'
    SET is no standard option only in INSERT statement?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    Code:
    INSERT INTO TABLE SET (ID) SELECT ID1 FROM TABEL1
    But in this case there is no word 'VALUES'
    did you test it? that query won't work

    you need to become familiar with the mysql manual so that you can easily look up the actual syntax for the SELECT option of the INSERT statement -- you will find that SET is not allowed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    Quote Originally Posted by r937 View Post
    did you test it? that query won't work
    I was mistake that was late. This query works perfect without SET option. I always check the query before write anything about it. I will repeat what I wrote earlier there is no word VALUES in this INSERT statement.
    INSERT INTO TWTR (ID) SELECT ID FROM FVPOZ WHERE FVNAG=1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are right, the SELECT option of the INSERT statement does not include the VALUES keyword
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2012
    Posts
    91
    Without the option SET bit complicated to me life. I do not know how to make mixed data.
    Code:
    INSERT INTO TWT (ID,WT) (SELECT MAX(ID) FROM FVPOZ,'12');
    Error occurs in the place '12'

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to understand the syntax that you are writing

    SELECT ... FROM FVPOZ,'12'

    this attempts to join the FVPOZ table and the '12' table, which of course is wrong

    this is what you want --
    Code:
    INSERT 
      INTO twt 
         ( id
         , wt ) 
    SELECT MAX(id)
         , 12 
      FROM fvpoz
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2012
    Posts
    91
    I give up.
    Code:
    INSERT INTO FVPOZ (FVNAG,KOD,OPIS,KRAJ,WAGA,WARTOSC,WARTPLN) VALUES '_Indeks',SELECT ID,'7',SELECT ID,'12','8','0' FROM CN WHERE KOD='1',FROM KRAJE WHERE KOD='2';
    I have two SELECT ID and two FROM. From what you wrote, i conclude that FROM should be at the end of query. It is unlikely that the two expressions FROM found at the end.

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    read the manual, don't make your own syntax up.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    I give up.
    please, don't give up

    if you keep guessing, eventually you will get the right syntax

    only a couple hundred more permutations to try!!



    or, you could, you know, read da manual

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    you need to become familiar with the mysql manual so that you can easily look up the actual syntax for the SELECT option of the INSERT statement -- you will find that SET is not allowed
    you need to understand the syntax that you are writing
    or, you could, you know, read da manual
    Though I don't know your requirements throughly and there must be some ways to read manuals and to understand the right synax,
    it might be one of a starting point to follow those chains of syntax diagrams ...

    Note 1: Italics(e.g. SELECT ..., select_expr, table_references, ...) are need to see further more.

    MySQL :: MySQL 5.5 Reference Manual :: 13.2.5 INSERT Syntax
    Code:
    INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
        [ ON DUPLICATE KEY UPDATE
          col_name=expr
            [, col_name=expr] ... ]

    MySQL :: MySQL 5.5 Reference Manual :: 13.2.9 SELECT Syntax
    Code:
    SELECT
        [ALL | DISTINCT | DISTINCTROW ]
          [HIGH_PRIORITY]
          [STRAIGHT_JOIN]
          [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
          [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
        select_expr [, select_expr ...]
        [FROM table_references
        [WHERE where_condition]
    ...
    ...
        [FOR UPDATE | LOCK IN SHARE MODE]]
    MySQL :: MySQL 5.5 Reference Manual :: 13.2.9.2 JOIN Syntax
    Code:
    table_references:
        table_reference [, table_reference] ...
    
    table_reference:
        table_factor
      | join_table
    
    table_factor:
        tbl_name [[AS] alias] [index_hint_list]
      | table_subquery [AS] alias
    
    ...
    ...

    Note 2: Sorry! I might be seeing wrong reference for select_expr. I'm not so familar with MySQL.

    MySQL :: MySQL 5.5 Reference Manual :: 9.5 Expression Syntax
    Code:
    expr:
        expr OR expr
      | expr || expr
      | expr XOR expr
      | expr AND expr
      | expr && expr
      | NOT expr
      | ! expr
      | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
      | boolean_primary
    
    boolean_primary:
        boolean_primary IS [NOT] NULL
      | boolean_primary <=> predicate
      | boolean_primary comparison_operator predicate
      | boolean_primary comparison_operator {ALL | ANY} (subquery)
      | predicate
    
    comparison_operator: = | >= | > | <= | < | <> | !=
    
    predicate:
        bit_expr [NOT] IN (subquery)
      | bit_expr [NOT] IN (expr [, expr] ...)
      | bit_expr [NOT] BETWEEN bit_expr AND predicate
      | bit_expr SOUNDS LIKE bit_expr
      | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
      | bit_expr [NOT] REGEXP bit_expr
      | bit_expr
    
    bit_expr:
        bit_expr | bit_expr
      | bit_expr & bit_expr
      | bit_expr << bit_expr
      | bit_expr >> bit_expr
      | bit_expr + bit_expr
      | bit_expr - bit_expr
      | bit_expr * bit_expr
      | bit_expr / bit_expr
      | bit_expr DIV bit_expr
      | bit_expr MOD bit_expr
      | bit_expr % bit_expr
      | bit_expr ^ bit_expr
      | bit_expr + interval_expr
      | bit_expr - interval_expr
      | simple_expr
    
    simple_expr:
        literal
      | identifier
      | function_call
      | simple_expr COLLATE collation_name
      | param_marker
      | variable
    ...
    ...
    so on...
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •