On Friday 20 December 2002 10:30, Thierry Michalowski wrote:
> I'm trying to run the following SQL statement on a table of my MySQL DB:
>
> insert into MYTABLE(ID, VALUE, SORTKEY)
> select T.ID, 'to to', MAX(T.SORTKEY) + 1 from MYTABLE as T group by
> T.ID
>
> This was suggested by a SQL expert (which I'm unfortunately not) , and
> works perfectly on MS SQL Server. :-(
> But, on MySQL it raises the following error:
> ERROR 1093: INSERT TABLE 'mytable' isn't allowed in FROM table list
>
> The following statement:
> insert into MYTABLE(ID, VALUE, SORTKEY) select ID, 'to to', MAX(SORTKEY) +
> 1 from MYTABLE group by ID
> raises the following error:
> ERROR 1066: not unique table/alias: 'mytable'
> After inserting the first "to to" value in a new row with the first ID
> value and a correctly incremented sortkey.
>
> I'm running MySQL-MAX 3.23.53 on Windows NT 4.0 SP6.
>
> What am I actually trying to do?
You can't insert data in the same table from which you retrieve:
http://www.mysql.com/doc/en/INSERT_SELECT.html
> Well, let me explain:
>
> I have a table MYTABLE consisting of 3 fields: id , value , sortkey
> id is an integer, not unique .
> value is an arbitrary char value , like "M1" or "-- --"
> sortkey is an integer which is used to sort the values associated with an
> id.
>
> Table example:
> 3,"--",0
> 3,"M1",2
> 3,"M2",3
> 6,"--",0
> 7,"KA",0
> 7,"A trick",1
> ...
>
> What I need to do is: Insert a new value, associated with every available
> id , with a sortkey equal to the maximum sortkey for this id plus 1 . Like
> if I want to insert "to to" , it would end in my example table as:
>
> 3,"--",0
> 3,"M1",2
> 3,"M2",3
> 3,"to to",4
> 6,"--",0
> 6,"to to",1
> 7,"KA",0
> 7,"A trick",1
> 7,"to to",2
> ...
>
> So, can you SQL and MySQL experts please tell me :
> 1-What did my friend do wrong that does not work on MySQL?
> 2-How the hell am I supposed to solve my problem in SQL only? (I'm mostly
> interested into this one, of course!)
Pick up your data into temporary table and then insert them into original.
table.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
