英文:
How do I bring data from one SQL table and column into the same column depedent on specific contraints?
问题
例如，我想要传输的数据位于Table1中的example_Date列中。所涉及的应用程序使用此列来存储多个数据字段ID的数据。
Table1:
表ID 字段ID 对象ID 对象Inv example_Date
--------------------------------------------------
1 2 1 3 01/01/1996
1 4 1 3 01/02/1996
1 6 1 3 01/01/1996
1 2 2 4 01/05/1996
1 4 2 4 01/01/1997
1 6 2 4 10/01/1998
然后假设我想要获取具有字段ID为2的example_date中的数据，并将其复制到具有以下约束的同一表中的任何内容：
- 对象ID = 对象ID
- 对象Inv = 对象Inv
- 字段ID = 6
结果将如下所示（*表示已更新的数据）
Table1:
表ID 字段ID 对象ID 对象Inv example_Date
-------------------------------------------------
1 2 1 3 01/01/1996
1 4 1 3 01/02/1996
1 6 1 3 *01/01/1996
1 2 2 4 01/05/1996
1 4 2 4 01/01/1997
1 6 2 4 *01/05/1996
任何帮助将不胜感激！
示例1（不起作用）：
UPDATE ValInvData
SET ValStr = ValStr
WHERE ValInvData.FieldId = 11061
AND ValInvData.ObjID = ValInvData.ObjId
AND ValInvData.ObjInv = ValInvData.ObjInv
AND ValInvData.FieldId != 11051
AND ValInvData.FieldId != 11061
AND ValInvData.FieldId != 11062
AND ValInvData.FieldId != 11055
AND ValInvData.FieldId != 11228
AND ValInvData.FieldId != 11230
AND ValInvData.FieldId != 14676;
示例2（不起作用）：
UPDATE ValInvData
SET ValStr = (SELECT ValStr
FROM ValInvData
WHERE Valdata.FieldId = 11061
AND ObjData.ObjID = ValData.ObjId
AND ValInvData.ObjInv = ValInvData.ObjInv
AND ValInvData.FieldId != 11051
AND ValInvData.FieldId != 11061
AND ValInvData.FieldId != 11062
AND ValInvData.FieldId != 11055
AND ValInvData.FieldId != 11228
AND ValInvData.FieldId != 11230
AND ValInvData.FieldId != 14676);
最终解决方案：
update ValInvData
set ValStr = coalesce(
(
select min(ValStr) -- 保证只返回一个值，
-- 可能最好抛出错误??
from ValInvData as vid
where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv and vid.FieldId = 11061
),
ValInvData.ValStr) -- 也可以使用exists子查询来确保匹配行可用
where FieldId = 11229;
英文:
For example the data I want to bring over resides in Table1, in column example_Date. The application in question uses this column to house data for multiple data field ID's.
Table1:
Table ID Field ID Obj ID Obj Inv example_Date
--------------------------------------------------
1 2 1 3 01/01/1996
1 4 1 3 01/02/1996
1 6 1 3 01/01/1996
1 2 2 4 01/05/1996
1 4 2 4 01/01/1997
1 6 2 4 10/01/1998
Then let's say I want to take the data in example_date that has a Field ID of 2 and copy it over to anything in the same table that has the following constraints:
- Obj ID = Obj ID
- Obj Inv = Obj Inv
- Field ID = 6
And the result would look like the below ( * = the updated data)
Table1:
Table ID Field ID Obj ID Obj Inv example_Date
-------------------------------------------------
1 2 1 3 01/01/1996
1 4 1 3 01/02/1996
1 6 1 3 *01/01/1996
1 2 2 4 01/05/1996
1 4 2 4 01/01/1997
1 6 2 4 *01/05/1996
Any help would be highly appreciated!
Example 1 (does not work):
UPDATE ValInvData
SET ValStr = ValStr
WHERE ValInvData.FieldId = 11061
AND ValInvData.ObjID = ValInvData.ObjId
AND ValInvData.ObjInv = ValInvData.ObjInv
AND ValInvData.FieldId != 11051
AND ValInvData.FieldId != 11061
AND ValInvData.FieldId != 11062
AND ValInvData.FieldId != 11055
AND ValInvData.FieldId != 11228
AND ValInvData.FieldId != 11230
AND ValInvData.FieldId != 14676;
Example 2 (does not work):
UPDATE ValInvData
SET ValStr = (SELECT ValStr
FROM ValInvData
WHERE Valdata.FieldId = 11061
AND ObjData.ObjID = ValData.ObjId
AND ValInvData.ObjInv = ValInvData.ObjInv
AND ValInvData.FieldId != 11051
AND ValInvData.FieldId != 11061
AND ValInvData.FieldId != 11062
AND ValInvData.FieldId != 11055
AND ValInvData.FieldId != 11228
AND ValInvData.FieldId != 11230
AND ValInvData.FieldId != 14676);
Final Solution
update ValInvData
set ValStr = coalesce(
(
select min(ValStr) -- to guarantee only one value returned,
-- possibly better to error??
from ValInvData as vid
where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv and vid.FieldId = 11061
),
ValInvData.ValStr) -- could also use exists subquery to ensure matching row is available
where FieldId = 11229;
答案1
得分: 1
更新 ValInvData
设置 example_date = coalesce(
(
选择 min(example_date) -- 以确保只返回一个值，
-- 也许最好报错？？
从 ValInvData 作为 vid
其中 vid.ObjId = ValInvData.ObjId 和 vid.ObjInv = ValInvData.ObjInv
并且 vid.FieldId = 2
),
example_date) -- 也可以使用存在子查询来确保匹配的行可用
其中 FieldId = 6;
英文:
update ValInvData
set example_date = coalesce(
(
select min(example_date) -- to guarantee only one value returned,
-- possibly better to error??
from ValInvData as vid
where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv
and vid.FieldId = 2
),
example_date) -- could also use exists subquery to ensure matching row is available
where FieldId = 6;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论