本文介绍了我如何写这个SQL UPDATE查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 说我有两个表: new_dogs< --------- 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---------namebreedcolorlocationfound_datedog_locations <<---- CONTAINS A HISTORY OF ALL DOGS EVER SPOTTED---------------namebreedlocationfrom_datethru_dateThe 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 done3rd 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_dateFROM dog_locations dlWHERE 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 dlWHERE 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));CheersAnjaBut you should really rethink your database design and follow LukLeds idea introducing a table dogs... 这篇关于我如何写这个SQL UPDATE查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!
09-25 00:28