我如何写这个SQL UPDATE查询？ [英] How would I write this SQL UPDATE query?

问题描述

说我有两个表：

  new_dogs< < -  CONTAINS DOGS SPOTTED TODAY 
 --------- 
 name 
 breed 
 color 
 location 
 found_date 
 
 dog_locations<< --- - 包含所有狗的历史记录
 --------------- 
 name 
 breed 
 location 
 from_date 
 thru_date 
  

new_dogs表中填充了今天发现的狗。说我在公园连续3天找到了一只名为max的白色狮子狗。在第一天，max被插入到公园的dog_locations表中，并且具有from_date of found_date。

第二天最大值在公园处显示为静止，因此不需要完成

第三天的最大值不再在new_dogs表（可以被称为dogs_found_today表），意味着他不再在公园。这意味着他在dog_locations中的输入不再有效，所以我想关闭他的thru_date。

我需要做的是更新thru_date dog_locations表，但不存在于new_dogs表中，并且thru_date为NULL。 thru_date将设置为CURRENT_DATE（）

每个dog_location必须是唯一的，主键为（name; breed; location; from_date）

我不知道如何去处理这个。

我可以选择dog_locations但不是new_dogs的狗：

  SELECT名称，品种，位置，from_date 
 FROM dog_locations dl 
 WHERE NOT EXISTS ，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））; 
  
 
 
解决方案
 
  UPDATE dog_locations SET thru_date =< actualdate或任何日期> 
 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））; 
  
干杯
 Anja 
 
 
 
但你应该真正重新思考你的数据库设计，并按照LukLeds的想法介绍一个表狗... 
 
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));

解决方案
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...

                        
这篇关于我如何写这个SQL UPDATE查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！