maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Federico!

On Dec 13, Federico Razzoli wrote:
> INSERT SELECT is not standard SQL. But there is no reason to have a duplicate value error.

Why do you think it's not? Here, in sql:2003 standard, part 2:

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

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

<from subquery> ::= [ <left paren> <insert column list> <right paren> ]
                    [ <override clause> ]
                    <query expression>

<query expression> ::= [ <with clause> ] <query expression body>

and so on, from <query expression body> via <query term> to <query primary>
to <simple table> to <query specification>, which is

<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

So, INSERT INTO ... SELECT seems to be a valid standard syntax.

Regards,
Sergei

> --------------------------------------------
> Sab 13/12/14, Roberto Spadim <roberto@xxxxxxxxxxxxx> ha scritto:
> 
>  Oggetto: [Maria-discuss] Doubt about 'atomic' insert
>  A: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>  Data: Sabato 13 dicembre 2014, 19:13
>  
>  Hi guysi'm
>  with a doubt about the "standard" (sql standand?)
>  error reporting , about INSERT SELECT...
>  should this insert select return duplicate
>  key?INSERT INTO errorsX (id) SELECT MAX(id)+1
>  FROM errorsX;
>  
>  considering:----CREATE
>  TABLE errorsX( id INT NOT NULL DEFAULT
>  0, PRIMARY KEY (id))
>  ;----