使用sql中另一个表中的值更新一个表 [英] update one table using values from another table in sql

问题描述

我必须在sql中更新一个表
我正在使用以下查询:

Hi,
I have to update one table in sql
i am using the following query:

UPDATE rdc_lb
 set
 rdc_lb.mrop=(select makeready.mrop from makeready where makeready.date=rdc_lb.date and makeready.shift=rdc_lb.shift and makeready.ns=rdc_lb.ns)

但效果不佳.

如何更新表格?

谢谢您

but it is not working fine.

How can I update the table?

Thank You

推荐答案

您的语法正确(只要表实际上具有您指定的字段).我在自己的系统上复制了您在此处拥有的查询，该查询已按预期运行.因此，我的假设是问题出在数据上.您的数据很可能不完全匹配或重复.让我解释一下每种可能性.

您的数据可能不匹配的一种方式是，如果日期字段存储的是未从一个人复制到另一个人的实际日期.日期字段通常存储整个日期/时间值，这意味着两个值可能在同一天，但由于它们在时间上相差一秒(或更少，取决于日期类型的精度)而不会相等.您应该检查一下日期是如何存储的.这将是一个昂贵的查询，但您可能只需要解析每个值的日期部分并进行比较(或创建一个计算字段).

与您的条件匹配的重复行也将是一个问题.如果您有两行符合您的条件，则子查询将返回两行或更多行.那不行. SQL将崩溃.要解决此问题，您可以将子查询限制为TOP 1行，如下所示:

Your syntax is correct (as long as the tables actually have the fields you specified). I replicated the query you have here on my own system and it ran as expected. My assumption, then, is that the issue is with the data. Most likely your data doesn''t quite match or you have a duplicate. Let me explain each possibility.

One way your data might not match is if the date fields are storing actual dates that weren''t copied from one to the other. Date fields usually store the entire date/time value, which means that two values might be on the same day but won''t be equal because they are one second different in time (or less depending on the precision of your date type). You should check to see how the dates are stored. It would be an expensive query, but you might have to parse out just the date portion of each value and compare them (or create a calculated field).

Duplicate rows that match your criteria will also be a problem. If you have two rows that match your criteria then the sub-query will return two or more rows. That isn''t ok. SQL will crash. To fix this, you could limit your sub-query to the TOP 1 rows like so:

UPDATE rdc_lb
SET rdc_lb.mrop=(SELECT TOP 1 makeready.mrop FROM makeready WHERE makeready.date=rdc_lb.date and makeready.shift=rdc_lb.shift AND makeready.ns=rdc_lb.ns)

仅在可以选择第一行的情况下才有效(例如，如果第二行将具有相同的数据).否则，您将需要找到另一种方法来找出要选择的行(可能根据创建日期以降序排列，然后选择顶部的行，以便获得最新的值).

That only works if it is ok to choose only the first row (if the second row would have the same data, for instance). Otherwise, you are going to need to find a different way to figure out which row to choose (maybe order them in descending order based upon creation date and then select the top one so you get the latest value).

亲爱的朋友，

希望此示例查询对您有所帮助.

Dear Friend,

Hope this sample query will be helping you.

update BLL set status='CLOSE' from
--select * from
BLL a (nolock), BML b (nolock) where a.ID=b.ID

问候，
AP

Regards,
AP

这篇关于使用sql中另一个表中的值更新一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！