Subquery won't allow the same table to be used

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Subquery won't allow the same table to be used

Post 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?
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
Post Reply