Databases
StackOverflow2013
Postsdbo
POHow would I write this SQL UPDATE query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow would I write this SQL UPDATE query?
    primarykey
    data
    text
    <p>Say I have two tables:</p> <pre><code>new_dogs &lt;&lt;---- CONTAINS DOGS SPOTTED TODAY --------- name breed color location found_date dog_locations &lt;&lt;---- CONTAINS A HISTORY OF ALL DOGS EVER SPOTTED --------------- name breed location from_date thru_date </code></pre> <p>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.</p> <p>2nd day max shows up as still at the park so nothing needs to be done</p> <p>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.</p> <p>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()</p> <p>Each dog_location must be unique with the primary key being (name;breed;location;from_date)</p> <p>I do not know how to go about this one.</p> <p>I am able to select dogs that are in dog_locations but not in new_dogs like this:</p> <pre><code>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)); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMikeJerome
    1. USMikeJerome
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI 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.
      singulars
      1. POHow would I write this SQL UPDATE query?
      1. USMikeJerome
    2. COPlease clarify something: you said to update dogs that do not occur in the new_dogs table but then said to use the new_dogs.found_date as the new thru_date. If no record exists for that dog how can we use that found date?
      singulars
      1. POHow would I write this SQL UPDATE query?
      1. USdavidethell
    3. CO@davidethell I have updated the question to say that it should be set to CURRENT_DATE()
      singulars
      1. POHow would I write this SQL UPDATE query?
      1. USMikeJerome
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload