On Wed, Jun 8, 2011 at 4:57 PM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi everyone,
>
> A bit of help please. This query works for me, and gives me 332,000
> rows of records out of my table of 400,000 rows. It takes about a
> minute to run.
>
> SELECT vehicle.*
> FROM vehicle, incidents
> WHERE incidents.key = vehicle.incident_link;
>
> Would I would like to do however, is remove the data from this table
> that does not match the above query. So to start with, I thought that
> I'd better try and identify this data using the query below. You'll
> note I have simply changed the '=' for '!=' instead.
>
> SELECT vehicle.*
> FROM vehicle, incidents
> WHERE incidents.key != vehicle.incident_link;
>
> However when I run this query, after about 10-15 minutes, I still
> don't have a result. I don't really understand why not.
You're getting a combination of every record from vehicle combined
with every record incidents except in the very specific case where the
identifiers match -- not want you want. There are a ton of ways to do
what you want in sql. Typically the best/fastest is left join/not
null:
if you are trying to find vehicles with an incident key that is not in
the incident table:
SELECT vehicle.*
FROM vehicle LEFT JOIN incidents ON incidents.key = vehicle.incident_link
WHERE incidents.key IS NULL
Incidents with no record in the vehicle table:
SELECT incidents .*
FROM incidents LEFT JOIN vehicle ON incidents.key = vehicle.incident_link
WHERE vehicle.incident_link IS NULL
Another way to do it is with 'where not exists' -- which is a lot
easier to fold into delete syntax:
SELECT vehicle.*
FROM vehicle
WHERE NOT EXISTS
(
SELECT 1 FROM incidents WHERE incidents.key = vehicle.incident_link
)
which you can turn into:
DELETE FROM vehicle
WHERE NOT EXISTS
(
SELECT 1 FROM incidents WHERE incidents.key = vehicle.incident_link
)
etc (don't jump directly to the delete, test it first!)
merlin
