SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Select into outfile, and then delete the selected rows

    Hi guys.

    I've just put together a really simple stored-procedure that will run every couple of minutes which creates an outfile:
    Code MySQL:
    BEGIN
    	SET @sql_text = CONCAT ("SELECT `out_line` into outfile 'wms-in."
    					,	DATE_FORMAT( NOW(), '%Y%m%d%H%i%s')
    					,	"' LINES TERMINATED BY '\n'"
    					,	" FROM jobs_out WHERE `scheduled` < now();");
    	PREPARE s1 FROM @sql_text;
    	EXECUTE s1;
    	DROP PREPARE s1;
    END
    From this we're generating a filename with a datetime stamp in the filename as required by the system that is reading it and it only reads in lines that are scheduled to be read (there is a 15 minute delay that is put on every entry via the `scheduled` field.)

    This all works perfect, just as we need it to, but the problem that I now have is that the row stays there forever. I need to remove it once it's been read in to the file. How best to do this?

    Cheers

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    A quick thought: If I was to select the PK of every row that I'll want in the outfile, and put the PK in to a variable in a format suitable to go in to an IN() function, I could then do the above query but the WHERE part of the query would be "WHERE id IN(@invar)", which I could then use again in a delete query. Is that a sensible way to do it?

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, forget that one, you can't return more than one result at a time in the SP.

    I worked it out myself by starting off setting @timenow as now(), then selecting everything where the time is < @timenow, and then deleting every record that is < @timenow. Seems to work a treat


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •