How would I write this SQL UPDATE query?

Question

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));

I was able to select all the dogs that were in dog_locations and not in new_dogs, but I couldn't hook up an Update to it. — MikeJerome
– MikeJerome, Commented Oct 30, 2011 at 0:58
@HillBilly.Developer I have added the select statement to the question. — MikeJerome
– MikeJerome, Commented Oct 30, 2011 at 1:22
You are having SQL problems, because your database design is just poor. Dogs should not be identified by name/breed pair, you probably should have a table called dogs. Primary keys should not be related to data, just identity types. Proper database design solves a lot of problems. — LukLed
– LukLed, Commented Oct 30, 2011 at 2:08

Anja · Accepted Answer · 2011-10-31 12:14:41Z

1

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));

Cheers Anja

But you should really rethink your database design and follow LukLeds idea introducing a table dogs...

answered Oct 31, 2011 at 12:14

Anja

3241 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How would I write this SQL UPDATE query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related