Page 1 of 1

Subquery won't allow the same table to be used

Posted: Sun Jul 22, 2007 9:19 pm
by superdezign
I'm extremely new to sub queries, so I'm not too clear on their capabilities. I have learned, however, that this is not acceptable:

Code: Select all

INSERT INTO `table` SET `current` = (SELECT COUNT(*) FROM `table` WHERE `user` = 1) + 1;
This query is meant to give new additions to a table a value that is one more than the current number of entries owned by the user. I can't use auto_increment since all user data is in the same table, so I thought that a sub query would do it for me.

It turns out that I'm not allowed to do a sub query on the same table as the outer query is being performed on, so I'm stumped. Any suggestions on a way to do this?

Posted: Mon Jul 23, 2007 12:18 am
by Zoxive
A little google searching found something like this..

Code: Select all

insert into table(current) select (max(id)+1) from table;
http://bugs.mysql.com/bug.php?id=3575

Another work around

Posted: Mon Jul 23, 2007 6:30 am
by superdezign
Thank you so much. :-D The second method was a failure, but the first one works like a charm.

For anyone that doesn't understand what the first example is doing, instead of selecting values and defining them (i.e. INSERT INTO table (col1, col2) VALUES (val1, val2); ), it is actually selecting a *row* to define, and then defining that whole row with another row that is created from a select statement.

So, to use it, you could:

Code: Select all

INSERT INTO `table` (data1, data2, current) SELECT 'val1', 'val2', (COUNT(*) + 1) FROM `table` WHERE `user` = 1;
Thanks a lot for the tip. ^_^
SQL makes more and more sense each time I hit a problem like this.