James Black <[EMAIL PROTECTED]> wrote on 01/20/2005 02:44:41 PM:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Philippe Poelvoorde wrote:
> |
> | Insert into comments(list of you fields here )
> | select 0,1,null,'testuser','testcomment',max(idx) from comments
> | where sourceid=1;
> |
> | but querying max(idx) is a bad idea. you could get the last inserted
id
> | with the following command :
> | SELECT last_insert_id() from comments LIMIT 1;
> |
> | the last_insert_id is available on a per connection basis.
> |
> | but why are you inserting back in comments a field from comments ???
> |
>
> ~ I looked in the archive for anything about "SQL schema", but there
> were just two messages, and the original message had no thread with it.
>
> ~ I took your suggestion, and tried the following, but got the same
> error message:
> INSERT INTO comments (idx,sourceid,created,userid,comment,parentid)
> VALUES(0,1,NULL,'testuser','this is a test',(SELECT max(idx) FROM
> comments WHERE sourceid=1))
>
> ~ The error is still:
> You can't specify target table 'comments' for update in FROM clause
>
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black [EMAIL PROTECTED]
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.5 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFB8AopikQgpVn8xrARAjI2AJ4se/25M8AcUvtO9Q8GAFe62/aIUQCgjVOJ
> CPYRiuFlXtm+MXhytxo5YKc=
> =mWUq
> -----END PGP SIGNATURE-----
James,
You should already KNOW the parent comment of a comment when you create
it. Don't you? Imagine the following thread:
TOPIC (1)
Comment on Topic (2)
Comment on Topic (3)
Someone comes along and wants to add a new comment to the main TOPIC
message. The value you need for your "parent" field is 1 not MAX(ID) which
would be 3.
Someone else wants to add a comment to the first comment (#2). It's parent
value needs to be 2 NOT the MAX(ID) which would now be 4 (thanks to the
addition that just happened).
Do you now understand why we are all questioning WHY you are trying to use
the MAX(ID) value? That's not something you look up but supply as part of
your data. When someone clicks on a topic/comment to generate a new
comment, you should already have the value of the parent topic's ID. No
look-ups required.
Maybe I am just missing the purpose of the field you are trying to
populate.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
