更新语法有问题 [英] Trouble with Update syntax
问题描述
我是SQL的新手，无法弄清楚如何更新我的表
（StoreItemStatus），其中包含每个项目的当前状态
store（STORE_KEY，ITEM_KEY，STATUS，...）。
我在表格中获得更新的状态信息（我称之为NewInfo）
有类似的字段。 NewInfo可能包含每个
商店/项目的多个记录，但我将使用最新状态。我不确定如何使用NewInfo的每条记录更新StoreItemStatus
。任何建议都是
非常感谢
谢谢，
Paul
I''m new to SQL and can''t figure out how to update my table
(StoreItemStatus) that contains the current status for items in each
store (STORE_KEY, ITEM_KEY, STATUS,...).
I get updated status info in a table (I''ll call it NewInfo) that
has similar fields. NewInfo may contain multiple records for each
Store/Item, but I will just use the latest status. I''m not sure how
to update StoreItemStatus using each record of NewInfo. Any advice is
greatly appreciated
Thanks,
Paul
推荐答案
你说你想要最新的状态，但你还没告诉我们如何确定最新的行是多少。我假设NewInfo有一个DATETIME列
（status_dt）并且（store_key，item_key，status_dt）是唯一的。
UPDATE StoreItemStatus
SET状态=
（选择状态
来自NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
（SELECT MAX（status_dt）
来自NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key））
-
David Portas
SQL Server MVP
-
You''ve said you want the "latest" status but you haven''t told us how to
determine which the latest row is. I''ll assume NewInfo has a DATETIME column
(status_dt) and that (store_key, item_key, status_dt) is unique.
UPDATE StoreItemStatus
SET status =
(SELECT status
FROM NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
(SELECT MAX(status_dt)
FROM NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key))
--
David Portas
SQL Server MVP
--
2004年5月24日星期一07:08:14 + 0100，David Portas写道：
On Mon, 24 May 2004 07:08:14 +0100, David Portas wrote:
你说过你想要的是最新的状态，但你没有告诉我们如何确定最新的行。我假设NewInfo有一个DATETIME列
（status_dt）并且（store_key，item_key，status_dt）是唯一的。
更新StoreItemStatus
SET status =
（SELECT状态
FROM NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
（SELECT MAX（status_dt）
FROM NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key））
You''ve said you want the "latest" status but you haven''t told us how to
determine which the latest row is. I''ll assume NewInfo has a DATETIME column
(status_dt) and that (store_key, item_key, status_dt) is unique.
UPDATE StoreItemStatus
SET status =
(SELECT status
FROM NewInfo AS N
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key
AND status_dt =
(SELECT MAX(status_dt)
FROM NewInfo
WHERE store_key = N.store_key
AND item_key = N.item_key))
大卫，
除非OP确定每个Store / Item组合在NewInfo表中至少有一行
行，否则他最好添加
WHERE EXISTS
（SELECT *
来自NewInfo
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key）
来自这个查询。
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Hi David,
Unless the OP is sure that every Store / Item combination has at least one
row in the NewInfo table, he''d better add
WHERE EXISTS
(SELECT *
FROM NewInfo
WHERE store_key = StoreItemStatus.store_key
AND item_key = StoreItemStatus.item_key)
to this query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
谢谢！你的假设是正确的，我相信这会有效。
Thank you! Your assumptions were correct, and I believe this will work.
这篇关于更新语法有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
