以下场景的UPDATE语句 [英] UPDATE statement on below scenario
问题描述
我的临时表上有Delivery_No和Instrument_Code两列我希望在我的主FACT表上根据 Delivery_No
I have Delivery_No and Instrument_Code two columns on my temporary table which data I want to UPDATE on my main FACT table based on Delivery_No
我的临时表的输出如下所示:
Output of my temporary table looks like below :
Delivery_No Instrument_Code
Delivery_No Instrument_Code
3136983 52914
3136983 53975
$
3136983 53983
3136983 53984
3136983 < span style ="white-space:pre"> 53990
3136983 52914
3136983 53975
3136983 53983
3136983 53984
3136983 53990
表示一个 Delivery_No (3136983)您可以看到多个 Instrument_Code。
Means, one Delivery_No (3136983) having multiple Instrument_Code as you can see.
现在,我的事实表中已有的列已经相同而且只有Delivery_No。
Now my Fact table is having existing column already which is same Delivery_No only.
然后在我的Fact表中我引入了新列,我将其命名为 Instrument_Code
and then in my Fact table I have introduced the new column and i call it same name as Instrument_Code
所以现在我的目标是更新此事实表的 Instrument_Code 与临时表的 Instrument_Code相同,基于 Delivery_No 。
So now my target is to UPDATE this Fact table's Instrument_Code to be same as temporary table's Instrument_Code based on Delivery_No .
我正在使用的当前UPDATE语句给出了Fact table UPDATE的输出,如下所示:
The current UPDATE statement I am using is giving me output of Fact table UPDATE as below :
事实表输出:
Delivery_No Instrument_Code
Delivery_No Instrument_Code
这意味着对于 Delivery_No = 3136983,它只从临时表中获取第一行
Instrument_Code ( 52914)并更新所有行使用相同数据的事实表是 52914
That means for the Delivery_No = 3136983 , it takes only first line of Instrument_Code (52914) from temporary table and updates all lines of Fact table using the same data which is 52914
理想情况下,我的事实表输出应如下所示, 与临时表格相同:
Delivery_No Instrument_Code
Delivery_No Instrument_Code
3136983 52914
3136983 53975
3136983 53983
3136983 53984
3136983 53990
3136983 52914
3136983 53975
3136983 53983
3136983 53984
3136983 53990
希望很清楚。有人可以帮忙吗?
Hope it is clear. Can anyone assist ?
推荐答案
基于给定的信息,这实际上是不可能的,因为您的新数据具有订单标准,现有的旧数据中不存在。只要没有涉及的其他密钥,您需要一个人工的,如ROW_NUMBER(),但是这个
意味着您将以任意方式将仪器代码分配给现有行。
Based on the given information, this is not really possible, cause your new data has an order criteria, which does not exists in the existing, old data.. as long as there is no further key involved, you need an artificial one, like ROW_NUMBER(), but this means that you'll assign the instrument code in an arbitrary way to the existing rows.
这篇关于以下场景的UPDATE语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!