Subquery won't allow the same table to be used
Posted: Sun Jul 22, 2007 9:19 pm
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:
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?
Code: Select all
INSERT INTO `table` SET `current` = (SELECT COUNT(*) FROM `table` WHERE `user` = 1) + 1;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?