Professional Web Applications Themes

deleting using temporary tables - MySQL

I'm trying to clean up a table that has been used to store versions of some text until they are pronounced status='ready' Once ready all the previous versions can be deleted. There can be many versions of the story all with the same story_id but different modify_dt. (that is: the index is story_id and modify_dt) Since I'm using MySql 4.0.27 which doesn't have nested queries I've figured out how to select what I need using temporary tables like so: CREATE TEMPORARY TABLE tmp ( story_id INT(11) DEFAULT 0 NOT NULL); INSERT INTO tmp SELECT story_id FROM story_versions WHERE status='ready'; SELECT ...

  1. #1

    Default deleting using temporary tables

    I'm trying to clean up a table that has been used to store versions of some
    text until they are pronounced status='ready'
    Once ready all the previous versions can be deleted. There can be many
    versions of the story all with the same story_id but different modify_dt.
    (that is: the index is story_id and modify_dt)
    Since I'm using MySql 4.0.27 which doesn't have nested queries I've figured
    out how to select what I need using temporary tables like so:

    CREATE TEMPORARY TABLE tmp (
    story_id INT(11) DEFAULT 0 NOT NULL);

    INSERT INTO tmp SELECT story_id FROM story_versions WHERE status='ready';

    SELECT story_id FROM story_versions, tmp WHERE tmp.story_id =
    story_versions.story_id AND status!='ready' AND story_id>180 LIMIT 0, 20;

    So this gets the story_id of stories that are set ready and have some
    versions that are != ready

    but I'm at a loss having found which ones, how to DELETE those entries from
    story_versions table by selecting from the tmp table and filtering only the
    !='ready' ones for deletion.
    It seems like I'm back to needing a sub-query to delete from one table by
    indexing from another.

    I'd appreciate any pointers.

    Thanks,

    Neville.


    n00bie Guest

  2. #2

    Default Re: deleting using temporary tables

    n00bie wrote:
     

    Could you not just use something similar to the following:

    DELETE
    FROM story_versions, tmp
    WHERE story_versions.story_id = tmp.story_id
    AND story_versions.status != "READY";

    --

    Murdoc Guest

  3. #3

    Default Re: deleting using temporary tables


    "Murdoc" <com> wrote in message
    news:connect.com.au...
    n00bie wrote:
     
    some 
    figured 
    from 
    the 

    Could you not just use something similar to the following:

    DELETE
    FROM story_versions, tmp
    WHERE story_versions.story_id = tmp.story_id
    AND story_versions.status != "READY";

    --

    I tried after fixing the other errors about story_id anbiguities in my op
    sql
    and it said:
    MySQL said:

    #1064 - You have an error in your SQL syntax near ' tmp
    WHERE story_versions.story_id = tmp.story_id
    AND story_versions.statu' at line 4

    So I'm geussing it doesn't like the cloaked sub query in there.
    But thanks for trying.





    n00bie Guest

  4. #4

    Default Re: deleting using temporary tables


    n00bie wrote: 

    I've assumed a table structure like this:

    stories(id*,story_id,status_id,trash)

    *=PRIMARY KEY and 'trash' is just for safety, as will become clear.

    So, if you have imagine a separate table for status - like this:

    status_id | status
    0 | just begun
    1 | in progress
    2 | ready

    then the most complete stories will have the highest status_id.

    A query like the one below will tease out the 'readiest' versions of
    each story, allowing you to then delete all those whose story_id's
    match those of the resulting table, but whose status_id's are lower.

    CREATE table tmp AS
    SELECT DISTINCT(s1.story_id), s1.status_id
    FROM stories s1
    LEFT JOIN stories s2 ON s1.id <> s2.id
    AND s1.story_id = s2.story_id
    AND s1.status_id < s2.status_id
    WHERE s2.status_id IS NULL
    ORDER BY story_id

    Also, as I said for safety's sake I've added a 'trash' column (DEFAULT
    = 0) to the stories table so that, just to confirm I'm right, you can
    do this:

    UPDATE stories,stmp SET stories.trash = 1 WHERE stories.story_id =
    stmp.story_id AND stories.status_id < stmp.status_id

    Clever, eh?

    strawberry Guest

  5. #5

    Default Re: deleting using temporary tables

    strawberry wrote: 
    >
    > I've assumed a table structure like this:
    >
    > stories(id*,story_id,status_id,trash)
    >
    > *=PRIMARY KEY and 'trash' is just for safety, as will become clear.
    >
    > So, if you have imagine a separate table for status - like this:
    >
    > status_id | status
    > 0 | just begun
    > 1 | in progress
    > 2 | ready
    >
    > then the most complete stories will have the highest status_id.
    >
    > A query like the one below will tease out the 'readiest' versions of
    > each story, allowing you to then delete all those whose story_id's
    > match those of the resulting table, but whose status_id's are lower.
    >
    > CREATE table tmp AS
    > SELECT DISTINCT(s1.story_id), s1.status_id
    > FROM stories s1
    > LEFT JOIN stories s2 ON s1.id <> s2.id
    > AND s1.story_id = s2.story_id
    > AND s1.status_id < s2.status_id
    > WHERE s2.status_id IS NULL
    > ORDER BY story_id
    >
    > Also, as I said for safety's sake I've added a 'trash' column (DEFAULT
    > = 0) to the stories table so that, just to confirm I'm right, you can
    > do this:
    >
    > UPDATE stories,stmp SET stories.trash = 1 WHERE stories.story_id =
    > stmp.story_id AND stories.status_id < stmp.status_id
    >
    > Clever, eh?[/ref]

    I'm not sure what the story_id>180 in the first post is about, but it seems
    that your suggestion does not look to ensure that there is a record with the
    status of "ready".
    I think that this query should do it in one:

    delete s1
    FROM story_versions s1, story_versions s2
    WHERE s1.story_id = s2.story_id
    AND s2.status = 'ready'
    and s1.status is null


    Paul Guest

  6. #6

    Default Re: deleting using temporary tables


    Paul Lautman wrote:
    it seems that your suggestion does not look to ensure that there is a
    record with the status of "ready".

    My suggestion doesn't. It just looks to see if there is a version that
    is 'more ready' than another version. If there is, then it marks all
    less ready versions for deletion.

    But your way works too, is simpler, and probably more in keeping with
    the OPs existing setup, although I think it should say:

    delete s1
    FROM story_versions s1, story_versions s2
    WHERE s1.story_id = s2.story_id
    AND s2.status = 'ready'
    and s1.status != 'ready' ?

    strawberry Guest

  7. #7

    Default Re: deleting using temporary tables

    strawberry wrote: 

    It did say that when I first tried it and it didn't work, that's why I had
    to change it to using "s1.status is null", then it worked.


    Paul Guest

  8. #8

    Default Re: deleting using temporary tables


    "Paul Lautman" <com> wrote in message
    news:net... 
    >
    > It did say that when I first tried it and it didn't work, that's why I had
    > to change it to using "s1.status is null", then it worked.
    >
    >[/ref]

    Thanks. You've both given me ideas about how to do it. I'll have to think
    about it some more, roll it round on my tongue, try all kinds of things then
    let you know if what succeeded.
    Thanks again. :-)


    n00bie Guest

  9. #9

    Default Re: deleting using temporary tables


    n00bie wrote: 
    > >
    > > It did say that when I first tried it and it didn't work, that's why I had
    > > to change it to using "s1.status is null", then it worked.
    > >
    > >[/ref]
    >
    > Thanks. You've both given me ideas about how to do it. I'll have to think
    > about it some more, roll it round on my tongue, try all kinds of things then
    > let you know if what succeeded.
    > Thanks again. :-)[/ref]
    I just worked out that the reason that I needed "s1.status IS NULL" is
    because in my database the status field had either NULL or 'ready'. If
    your status field allways has a value then Strawberry's amendment to my
    query is what you need. If is may be null or may have a value other
    than 'ready' then you need to combine the two thus:

    DELETE s1
    FROM story_versions s1, story_versions s2
    WHERE s1.story_id = s2.story_id
    AND s2.status = 'ready'
    AND (s1.status != 'ready' ? OR s1.status IS NULL)

    Captain Guest

  10. #10

    Default Re: deleting using temporary tables


    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... [/ref][/ref]
    a [/ref][/ref]
    that [/ref][/ref]
    with [/ref][/ref]
    had 
    > >
    > > Thanks. You've both given me ideas about how to do it. I'll have to[/ref][/ref]
    think [/ref]
    then 
    > I just worked out that the reason that I needed "s1.status IS NULL" is
    > because in my database the status field had either NULL or 'ready'. If
    > your status field allways has a value then Strawberry's amendment to my
    > query is what you need. If is may be null or may have a value other
    > than 'ready' then you need to combine the two thus:
    >
    > DELETE s1
    > FROM story_versions s1, story_versions s2
    > WHERE s1.story_id = s2.story_id
    > AND s2.status = 'ready'
    > AND (s1.status != 'ready' ? OR s1.status IS NULL)
    >[/ref]

    Thanks all, you put me on the right track.

    Here's what I came up with:

    # select all story versions with ready status
    CREATE TEMPORARY TABLE tmp1 SELECT story_id
    FROM story_versions
    WHERE STATUS = 'ready';

    # delete story_versions with status other than ready belonging to a story
    that is marked ready
    DELETE story_versions FROM story_versions,
    tmp1 WHERE story_versions.story_id = tmp1.story_id
    AND story_versions.status!= 'ready';

    I was forgetting that the index is a combination of the story_id and the
    modify_dt and I was unfamiliar with the multiple table delete syntax.

    Anyway: I could not have done it without your help. Thanks everyone.


    n00bie Guest

  11. #11

    Default Re: deleting using temporary tables


    Captain Paralytic wrote: 
    > >
    > > Thanks. You've both given me ideas about how to do it. I'll have to think
    > > about it some more, roll it round on my tongue, try all kinds of things then
    > > let you know if what succeeded.
    > > Thanks again. :-)[/ref]
    > I just worked out that the reason that I needed "s1.status IS NULL" is
    > because in my database the status field had either NULL or 'ready'. If
    > your status field allways has a value then Strawberry's amendment to my
    > query is what you need. If is may be null or may have a value other
    > than 'ready' then you need to combine the two thus:
    >
    > DELETE s1
    > FROM story_versions s1, story_versions s2
    > WHERE s1.story_id = s2.story_id
    > AND s2.status = 'ready'
    > AND (s1.status != 'ready' ? OR s1.status IS NULL)[/ref]

    I think my question mark has been taken out of context!!! :-)

    strawberry Guest

  12. #12

    Default Re: deleting using temporary tables

    n00bie wrote: 
    >> I just worked out that the reason that I needed "s1.status IS NULL"
    >> is because in my database the status field had either NULL or
    >> 'ready'. If your status field allways has a value then Strawberry's
    >> amendment to my query is what you need. If is may be null or may
    >> have a value other than 'ready' then you need to combine the two
    >> thus:
    >>
    >> DELETE s1
    >> FROM story_versions s1, story_versions s2
    >> WHERE s1.story_id = s2.story_id
    >> AND s2.status = 'ready'
    >> AND (s1.status != 'ready' ? OR s1.status IS NULL)
    >>[/ref]
    >
    > Thanks all, you put me on the right track.
    >
    > Here's what I came up with:
    >
    > # select all story versions with ready status
    > CREATE TEMPORARY TABLE tmp1 SELECT story_id
    > FROM story_versions
    > WHERE STATUS = 'ready';
    >
    > # delete story_versions with status other than ready belonging to a
    > story that is marked ready
    > DELETE story_versions FROM story_versions,
    > tmp1 WHERE story_versions.story_id = tmp1.story_id
    > AND story_versions.status!= 'ready';
    >
    > I was forgetting that the index is a combination of the story_id and
    > the modify_dt and I was unfamiliar with the multiple table delete
    > syntax.
    >
    > Anyway: I could not have done it without your help. Thanks everyone.[/ref]

    BUT YOU DON'T NEED THE TEMPORARY TABLE!

    The self join on the DELETE does it all in one query!


    Paul Guest

  13. #13

    Default Re: deleting using temporary tables


    "Paul Lautman" <com> wrote in message
    news:net... 
    > >
    > > Thanks all, you put me on the right track.
    > >
    > > Here's what I came up with:
    > >
    > > # select all story versions with ready status
    > > CREATE TEMPORARY TABLE tmp1 SELECT story_id
    > > FROM story_versions
    > > WHERE STATUS = 'ready';
    > >
    > > # delete story_versions with status other than ready belonging to a
    > > story that is marked ready
    > > DELETE story_versions FROM story_versions,
    > > tmp1 WHERE story_versions.story_id = tmp1.story_id
    > > AND story_versions.status!= 'ready';
    > >
    > > I was forgetting that the index is a combination of the story_id and
    > > the modify_dt and I was unfamiliar with the multiple table delete
    > > syntax.
    > >
    > > Anyway: I could not have done it without your help. Thanks everyone.[/ref]
    >
    > BUT YOU DON'T NEED THE TEMPORARY TABLE!
    >
    > The self join on the DELETE does it all in one query!
    >
    >[/ref]
    I see you are EXCITED ;-)
    Actually it does need the temp table...
    I guess I failed to explain the scenario well enough. My apologies.
    There are 3 states 'ready', 'pending' and 'killed' The killed state maybe
    revived to pending.
    The index is story_id + modify_dt
    As I understand the original intention of the person who made this table it
    was so you could revert to previous versions of a story using the story_id
    and the modify_dt.
    Now that things are filling up some they have asked me to clear superfluous
    stuff from the table and it has been agreed that the criteria is that once a
    story is marked 'ready' then we can dispense with all the 'pending' versions
    of _that_ story..

    Here's a short example:
    story_versions: # (ordered by story_id then modify_dt)
    story_id | modify_dt | status | content
    1 datetime1 pending this is a storeee
    1 datetime2 pending this is a stareee
    1 datetime3 pending this is almosta storeee
    1 datetime4 ready this is THE #1 storeee
    2 datetime4 pending this is storeee #2
    2 datetime5 pending this is storiee #2
    2 datetime6 killed this is storiee #2 killed
    3 datetime2 pending this is storiee #3
    3 datetime4 pending this be storiee #3
    3 datetime7 pending this be story #3
    3 datetime8 pending this are be storiee #3 matey

    So with a single DELETE using !='ready' would erase all of story 3
    (incorrect), all of story 2 (incorrect) and all the pending versions of
    story 1(correct)

    With the temp table filtering which story_id entries have 'ready' you can
    then use that to delete the other !='ready' with the same story_id
    So as I see it the temp table is needed unless you can do sub-queries which
    this version of MySql doesn't have.


    n00bie Guest

  14. #14

    Default Re: deleting using temporary tables


    n00bie wrote: 
    > >
    > > BUT YOU DON'T NEED THE TEMPORARY TABLE!
    > >
    > > The self join on the DELETE does it all in one query!
    > >
    > >[/ref]
    > I see you are EXCITED ;-)
    > Actually it does need the temp table...
    > I guess I failed to explain the scenario well enough. My apologies.
    > There are 3 states 'ready', 'pending' and 'killed' The killed state maybe
    > revived to pending.
    > The index is story_id + modify_dt
    > As I understand the original intention of the person who made this table it
    > was so you could revert to previous versions of a story using the story_id
    > and the modify_dt.
    > Now that things are filling up some they have asked me to clear superfluous
    > stuff from the table and it has been agreed that the criteria is that once a
    > story is marked 'ready' then we can dispense with all the 'pending' versions
    > of _that_ story..
    >
    > Here's a short example:
    > story_versions: # (ordered by story_id then modify_dt)
    > story_id | modify_dt | status | content
    > 1 datetime1 pending this is a storeee
    > 1 datetime2 pending this is a stareee
    > 1 datetime3 pending this is almosta storeee
    > 1 datetime4 ready this is THE #1 storeee
    > 2 datetime4 pending this is storeee #2
    > 2 datetime5 pending this is storiee #2
    > 2 datetime6 killed this is storiee #2 killed
    > 3 datetime2 pending this is storiee #3
    > 3 datetime4 pending this be storiee #3
    > 3 datetime7 pending this be story #3
    > 3 datetime8 pending this are be storiee #3 matey
    >
    > So with a single DELETE using !='ready' would erase all of story 3
    > (incorrect), all of story 2 (incorrect) and all the pending versions of
    > story 1(correct)
    >
    > With the temp table filtering which story_id entries have 'ready' you can
    > then use that to delete the other !='ready' with the same story_id
    > So as I see it the temp table is needed unless you can do sub-queries which
    > this version of MySql doesn't have.[/ref]

    No the temp table is NOT needed. The self join makes it only delete
    non-readys when there is already an entry that is ready. It replaces
    the temp table by using teh criteria for the temp table in the join
    condition. I promise you I tested it before I posted it! Try it out on
    some test data and you will see that it works.

    We are trying toteach you about how to use SQL and giving you skills in
    the use of joins here and you are totally ignoring what we are saying.

    Captain Guest

  15. #15

    Default Re: deleting using temporary tables


    Let me put this another way
     
    So who is telling you to do a "single DELETE using !='ready'"? Does the
    query I posted do a single delete with "single DELETE using !='ready'
    "? No it does not!

    It does a self join replacing the creation of the temporary table!
     
    Which is EXACTLY what the query with the self join that I posted does!
     
    Sub-queries are almost NEVER needed for this sort of task. Self JOINs
    or self LEFT JOINs are always faster and can almost always replace
    subqueries. There is a whole section in the MySQL manual just to
    explain this.
    http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html

    Captain Guest

  16. #16

    Default Re: deleting using temporary tables


    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    > So who is telling you to do a "single DELETE using !='ready'"? Does the
    > query I posted do a single delete with "single DELETE using !='ready'
    > "? No it does not!
    >
    > It does a self join replacing the creation of the temporary table!
    > [/ref]
    can 
    > Which is EXACTLY what the query with the self join that I posted does!
    > [/ref]
    which 
    > Sub-queries are almost NEVER needed for this sort of task. Self JOINs
    > or self LEFT JOINs are always faster and can almost always replace
    > subqueries. There is a whole section in the MySQL manual just to
    > explain this.
    > http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
    >[/ref]
    Well duh! You guys rule!
    Yes I'm thick but you are making headway now.
    Mea culpa for not reading and reading again and reading again and again what
    you said.
    You are all indeed teaching me new tricks. Thanks for persisting with this
    slow wit.
    That is excellent. I did not realise you could call the same table twice
    with different criteria within the query. Thanks.
    So in your query:
    DELETE s1
    FROM story_versions s1, story_versions s2
    WHERE s1.story_id = s2.story_id
    AND s2.status = 'ready'
    AND (s1.status != 'ready' ? OR s1.status IS NULL)

    What is the ? in the last line for?

    When I tell MySql 4.0.27 to do that it says:
    #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 '?
    OR s1.status IS NULL)' at line 5

    and if I take the ? out it says:
    #1066 - Not unique table/alias: 's1'

    I've gone round and round with this and I can't get it to work. So what am I
    doing wrong?
    (you could be forgiven for giving up on me at this stage :$ )

    Might be this: http://lists.mysql.com/bugs/15027 I don't get to choose what
    version is run :(
    the fickle finger ...sigh.

    Thanks :D




    n00bie Guest

  17. #17

    Default Re: deleting using temporary tables


    "n00bie" <com> wrote in message
    news:ehln05$9iq$server.aioe.org... [/ref][/ref]
    of 
    > > So who is telling you to do a "single DELETE using !='ready'"? Does the
    > > query I posted do a single delete with "single DELETE using !='ready'
    > > "? No it does not!
    > >
    > > It does a self join replacing the creation of the temporary table!
    > > [/ref]
    > can 
    > > Which is EXACTLY what the query with the self join that I posted does!
    > > [/ref]
    > which 
    > > Sub-queries are almost NEVER needed for this sort of task. Self JOINs
    > > or self LEFT JOINs are always faster and can almost always replace
    > > subqueries. There is a whole section in the MySQL manual just to
    > > explain this.
    > > http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
    > >[/ref]
    > Well duh! You guys rule!
    > Yes I'm thick but you are making headway now.
    > Mea culpa for not reading and reading again and reading again and again[/ref]
    what 
    '? 

    what 
    Ok i see the question mark taken out of context message now. newsreader
    didn't thread stuff right. Sorry for asking an already answered question.


    n00bie Guest

  18. #18

    Default Re: deleting using temporary tables


    "strawberry" <com> wrote in message
    news:googlegroups.com... [/ref][/ref]
    is a [/ref][/ref]
    that [/ref][/ref]
    all [/ref][/ref]
    with [/ref][/ref]
    I had [/ref][/ref]
    think [/ref][/ref]
    things then 
    > > I just worked out that the reason that I needed "s1.status IS NULL" is
    > > because in my database the status field had either NULL or 'ready'. If
    > > your status field allways has a value then Strawberry's amendment to my
    > > query is what you need. If is may be null or may have a value other
    > > than 'ready' then you need to combine the two thus:
    > >
    > > DELETE s1
    > > FROM story_versions s1, story_versions s2
    > > WHERE s1.story_id = s2.story_id
    > > AND s2.status = 'ready'
    > > AND (s1.status != 'ready' ? OR s1.status IS NULL)[/ref]
    >
    > I think my question mark has been taken out of context!!! :-)
    >[/ref]
    Thanks. That looks as if it woiuld work well once if I was able to use a
    veriosn of MySql >=4.1.1
    Sadly it looks like the version of MySql I'm working 4.0.27 with has a bug
    and can't do that even without the ?

    Thanks for sharing your wisdom. :-)



    n00bie Guest

Similar Threads

  1. Cannot work with temporary tables
    By Nafiganado in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 2nd, 03:25 PM
  2. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  3. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  4. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM
  5. Deleting Temporary Internet Files
    By Gary in forum Windows XP/2000/ME
    Replies: 6
    Last Post: July 17th, 03:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139