MySQL Delete, checking against temp. table

[cargo]

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

 

[Cromewell]

Try this:
delete from server_error_reasons
where
hostid
not in
select hostid from temp_sunward_audit;

 

[cargo]

'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

 

[apj101]

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

 

[cargo]

Nope it didn't work

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

 

[apj101]

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.