Archives

The List

« MySQL Pop Quiz #7 | Main | MySQL Pop Quiz #9 »

MySQL Pop Quiz #8

By Carsten | February 28, 2008

In Quiz #5, we looked at the following CREATE TABLE and INSERT:

CREATE TABLE test (
  id varchar(1) NOT NULL,
  PRIMARY KEY  (id)
);
INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');

The INSERT succeeds, and the table contains 5 rows.
The multi-row INSERT … VALUES (…), (…), … syntax is completely legal in MySQL. But…

  1. is it portable to other Relational Database Management Systems such as Oracle, MS-SQL, DB2, Firebird, Postgresql, …?
  2. is this standard SQL syntax?

Click here for the answer

If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting. More Pop Quiz entries.

Topics: MySQL, Pop Quiz |

6 Responses to “MySQL Pop Quiz #8”

  1. Björn Steinbrink Says:
    February 28th, 2008 at 9:35 am

    Postgres (tested with 8.2.6) accepts that, too

  2. Roland Bouman Says:
    February 28th, 2008 at 10:59 am

    “You might be surprised by this, but this syntax is in fact standard SQL, according to the SQL-99 standard.”

    heh, nice find. I didn’t know that.

  3. Roland Bouman Says:
    February 28th, 2008 at 11:03 am

    For posterity, here’s the majority of production rules from the relevant spec (ISO/IEC 9075-2:1999 (E)) (you can the bulk of it here
    http://www.ncb.ernet.in/education/modules/dbms/SQL99/):

    14.8 <insert statement>, p 673

    <insert statement> ::=
    INSERT INTO <insertion target>
    <insert columns and source>

    <insertion target> ::=
    <table name>

    <insert columns and source> ::=
    <from subquery>
    | <from constructor>
    | <from default>

    <from constructor> ::=
    [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <contextually typed table value constructor>

    7.3 <table value constructor>, p 227

    <contextually typed table value constructor> ::=
    VALUES <contextually typed row value expression list>

    <contextually typed row value expression list> ::=
    <contextually typed row value expression>
    [ { <comma> <contextually typed row value expression> }... ]

    7.2 <row value expression>, p226

    <contextually typed row value expression> ::=
    <row value special case>
    | <contextually typed row value constructor>

    <row value special case> ::=
    <value specification>
    | <value expression>

    7.1 <row value constructor>, p223

    <contextually typed row value constructor> ::=
    <contextually typed row value constructor element>
    | [ ROW ]
    <left paren>
    <contextually typed row value constructor element list>
    <right paren>

    <contextually typed row value constructor element list> ::=
    <contextually typed row value constructor element>
    [ { <comma> <contextually typed row value constructor element> }... ]

    <contextually typed row value constructor element> ::=
    <value expression>
    | <contextually typed value specification>

  4. PHP Encoder Says:
    March 5th, 2008 at 10:48 pm

    Nice to know that this is standard, I use it quite often.

  5. Propiedad Privada » Blog Archive » Test rápidos de MySQL - Test 8 Says:
    March 25th, 2008 at 10:55 am

    [...] Traducido con permiso expreso de Mysql Test Quiz 8 Original [...]

  6. Guerrero de Terracota » MySQL Pop Quiz #8 Says:
    March 31st, 2008 at 9:08 am

    [...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]

Comments