Hello,
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?
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!)
Thank you in advance for your help.
Best regards
Thierry Michalowski
---------------------------------------------------------------------
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
