Looks like you're missing a comma after "comm_id", before
the @num := line?
andy
Santosh Killedar wrote:
> I am trying the following code on 4.1.2 and getting a
> syntax error that I could not figure out. It works
> fine on 5.x. Any suggestion/alternate
>
> CREATE TEMPORARY TABLE Temp
> (Node INT,
> comm_id INT, INDEX USING BTREE (comm_id))
> ENGINE = MyISAM;
>
> INSERT INTO Temp
> SELECT recipient, id
> FROM `main_guestbook`
> ;
>
> set @Node := '', @num := 1;
> delete from main_guestbook where id in (
> select comm_id
> from (
> select Node, comm_id
> @num := if(@Node = Node, @num + 1, 1) as
> row_number,
> @Node := Node as dummy
> from Temp order by Node, comm_id desc
> ) as x
> where row_number > 500
> )
>
> MySQL said:
>
> #1064 - You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server
> version for the right syntax to use near '@num := if(
> @Node = Node , @num + 1 , 1 ) as row_number , @Node
> := Node as dumm' at line 1
>
>
>
> ---------------------------------------------
> I have a MYsql table with following columns Node ID,
> Comment ID, Text, Date. Coment ID is primary key. For
> each Node ID there are one or more comment IDs
> (comments). There is a threshold (max_comments) that a
> node can have. How can I delete oldest comments
> associated with those nodes where this threshold is
> surpassed, such that the number of comments again will
> be below threshold for the node. The threshold is
> common for all nodes but the current number of
> comments is not (since those were added before the
> threshold policy established). Please note I want to
> delete older comments first, only for nodes above
> threshold
>
> thanks
> Santosh Killedar
>
>
>
>
>
> ____________________________________________________________________________________
> Looking for last minute shopping deals?
> Find them fast with Yahoo! Search.
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping
>
--
Andy Wallace - CISData - IDX Slave
AIM: acmwallace awallace@stripped
