List:General Discussion« Previous MessageNext Message »
From:Santosh Killedar Date:February 18 2008 11:04pm
Subject:RE: MYSQL Limit
View as plain text  
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
Thread
MYSQL Limit Santosh Killedar17 Feb
RE: MYSQL Limit Santosh Killedar17 Feb
RE: MYSQL Limit Santosh Killedar19 Feb
  • Re: MYSQL LimitAndy Wallace19 Feb