Say I have two tables:
new_dogs <<---- CONTAINS DOGS SPOTTED TODAY --------- name breed color location found_date dog_locations <<---- CONTAINS A HISTORY OF ALL DOGS EVER SPOTTED --------------- name breed location from_date thru_date
The new_dogs table is populated with dogs found today. Say I found a white poodle named max at the park 3 days in a row. On the first day, max is inserted into the dog_locations table at the park with a from_date of found_date.
2nd day max shows up as still at the park so nothing needs to be done
3rd day max is no longer in the new_dogs table(which could be called the dogs_found_today table) meaning he is no longer at the park. This means that his entry in dog_locations is no longer valid, so i want to close his thru_date.
What I need to do is update the thru_date on dogs that exist in the dog_locations table, but do not exist in the new_dogs table and have a thru_date of NULL. The thru_date will be set to CURRENT_DATE()
Each dog_location must be unique with the primary key being (name;breed;location;from_date)
I do not know how to go about this one.
I am able to select dogs that are in dog_locations but not in new_dogs like this:
SELECT name, breed, location, from_date FROM dog_locations dl WHERE NOT EXISTS (SELECT name, breed, location, found_date FROM new_dogs nd WHERE (nd.name = dl.name) AND (nd.breed = dl.breed) AND (nd.location = dl.location) AND (nd.found_date = dl.from_date));Answer1:
UPDATE dog_locations SET thru_date = <actualdate or whichever date>
FROM dog_locations dl
WHERE NOT EXISTS (SELECT name, breed, location, found_date
FROM new_dogs nd
WHERE (nd.name = dl.name) AND (nd.breed = dl.breed)
AND (nd.location = dl.location)
AND (nd.found_date = dl.from_date));
But you should really rethink your database design and follow LukLeds idea introducing a table dogs...