MySQL Delete, checking against temp. table
Results 1 to 9 of 9

Thread: MySQL Delete, checking against temp. table

  1. #1
    Senior Member
    Join Date
    Apr 2004
    Posts
    228

    MySQL Delete, checking against temp. table

    Hi guys

    One more little thing I can't work out

    I need to delete the records from the table 'server_error_reasons', but it should be done only in the case if these servers do not appear in the temporary table 'temp_sunward_audit'

    the original query was:

    delete from server_error_reasons
    where
    server_error_reasons.hostid != all (select hostid from temp_sunward_audit)
    and
    server_error_reasons.hostname != all (select hostname from temp_sunward_audit);


    Now this one produces an error as it is calling on to the temp. table twice.

    At the moment, after some more research in to the subject I got the next query:

    delete server_error_reasons
    from server_error_reasons, temp_sunward_audit
    where
    temp_sunward_audit.hostid != server_error_reasons.hostid
    and
    temp_sunward_audit.hostname != server_error_reasons.hostname;

    and a couple of it's variations.

    Yet the query in it's present form deletes all the records from the 'server_error_resons' table.

    I understand that it has something to do with the way I join tables, yet it seems that I am not quite grasping the concept of joints correctly.

    Thanks for all your answers in advance
    Last edited by nightcat; December 18th, 2006 at 12:38 PM.
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  2. #2
    Senior Member alakhiyar's Avatar
    Join Date
    Dec 2006
    Location
    Land of Oryx
    Posts
    255
    Try this:
    delete from server_error_reasons
    where
    hostid
    not in
    select hostid from temp_sunward_audit;
    (\__/)
    (='.'=)
    (")_(")

  3. #3
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Quote Originally Posted by alakhiyar
    Try this:
    delete from server_error_reasons
    where
    hostid
    not in
    select hostid from temp_sunward_audit;
    'hostid' and 'hostname' are making up a unique key, so I have to check agains both of them. If I'll do a 'select' statement agains te temp table twice, MySQL comes up with an error.

    But you gave me an idea I'm going to try out
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  4. #4
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Nope it didn't work
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  5. #5
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Final query

    delete from server_error_reasons
    where
    CONCAT(server_error_reasons.hostid, server_error_reasons.hostname) not in (select CONCAT(hostid, hostname) from temp_sunward_audit);

    using concatonation meant that I only needed to call on to the temp table once
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  6. #6
    Senior Member alakhiyar's Avatar
    Join Date
    Dec 2006
    Location
    Land of Oryx
    Posts
    255
    delete
    from server_error_reasons
    where
    server_error_reasons.hostid
    not in
    (select hostid from temp_sunward_audit)
    AND
    server_error_reasons.hostname
    not in
    (select hostname from temp_sunward_audit);


    that should do it
    (\__/)
    (='.'=)
    (")_(")

  7. #7
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Quote Originally Posted by alakhiyar
    delete
    from server_error_reasons
    where
    server_error_reasons.hostid
    not in
    (select hostid from temp_sunward_audit)
    AND
    server_error_reasons.hostname
    not in
    (select hostname from temp_sunward_audit);


    that should do it
    No offence, but you realy didn't pay attention

    You repeated the original query I wrote, id does not work in MySQL server.
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  8. #8
    Senior Member alakhiyar's Avatar
    Join Date
    Dec 2006
    Location
    Land of Oryx
    Posts
    255
    i was thinking of using concat, it would have been my next idea. But be carefull to make sure your data is white space and null terminator free when entered.
    (\__/)
    (='.'=)
    (")_(")

  9. #9
    Senior Member
    Join Date
    Mar 2002
    Posts
    442
    sql: '<>' not equal
    '!=' is algol, you are using the wrong language

    you don't need the 'all'

Posting Permissions

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