64638

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

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

Cheers Anja

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

Recommend

  • Using Restrict method for emails within a specified date
  • How I can populate data in birt report by passing parameters in the URL
  • what's wrong with this Magento query?
  • 'void' type not allowed here (java) error [closed]
  • Get the empty records using SQL
  • DataSet Object for the Google Fit API
  • Dataflow failing to push messages to BigQuery from PubSub
  • Why does the “name” parameter to __setattr__ include the class, but __getattr__ doesn't?
  • What is the meaning of xs:mixed without elements?
  • Unable to retrieve full text of title from JPEG metadata
  • Covariance/Contravariance Conundrum when using generic interface constraints
  • How can I validate a date including “00:07 PM” in JavaScript?
  • Recursively defined nested types (in terms of incomplete types)
  • MySpace DOM?
  • What's the benefit of the trailing apostrophe in character literals
  • Rely on Facebook user id as a permanent user identifier
  • Does sql server minds the way records where inserted?
  • MySql - get days remaining
  • How to send control C to Mac Terminal using python?
  • Returning this from a constructor function in JS
  • Linux command line : edit hacked index files
  • CSS Grid, position absolute an element in a css grid item: IMPOSSIBLE
  • Using HTML/CSS for UI in XNA?
  • EntLib Way to Bind “Null” Value to Parameter
  • What is the use of a session store?
  • is there a way to update filter with async data
  • How to get the date of next specified day of week
  • XSLT foreach repeating nodes to flat
  • How to access culture data in globalize.js V1.0.0
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • How to assign byte[] as a pointer in C#
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • print() is showing quotation marks in results
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Apache 2.4 - remove | delete | uninstall
  • Unanticipated behavior
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app