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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
