如何根据特定约束条件将一个SQL表格和列中的数据导入到同一列中？

huangapple
go评论96阅读模式
英文:

How do I bring data from one SQL table and column into the same column depedent on specific contraints?

问题

例如，我想要传输的数据位于Table1中的example_Date列中。所涉及的应用程序使用此列来存储多个数据字段ID的数据。

Table1:

  1. 表ID  字段ID  对象ID  对象Inv  example_Date
  2. --------------------------------------------------
  3. 1         2         1       3        01/01/1996
  4. 1         4         1       3        01/02/1996
  5. 1         6         1       3        01/01/1996
  6. 1         2         2       4        01/05/1996
  7. 1         4         2       4        01/01/1997
  8. 1         6         2       4        10/01/1998

然后假设我想要获取具有字段ID为2的example_date中的数据，并将其复制到具有以下约束的同一表中的任何内容：

  1. 对象ID = 对象ID
  2. 对象Inv = 对象Inv
  3. 字段ID = 6

结果将如下所示（*表示已更新的数据）

Table1:

  1. 表ID  字段ID  对象ID  对象Inv  example_Date
  2. -------------------------------------------------
  3. 1         2         1       3        01/01/1996
  4. 1         4         1       3        01/02/1996
  5. 1         6         1       3       *01/01/1996
  6. 1         2         2       4        01/05/1996
  7. 1         4         2       4        01/01/1997
  8. 1         6         2       4       *01/05/1996

任何帮助将不胜感激！

示例1（不起作用）：

  1. UPDATE ValInvData
  2. SET ValStr = ValStr
  3. WHERE ValInvData.FieldId = 11061
  4.   AND ValInvData.ObjID = ValInvData.ObjId
  5.   AND ValInvData.ObjInv = ValInvData.ObjInv
  6.   AND ValInvData.FieldId != 11051
  7.   AND ValInvData.FieldId != 11061
  8.   AND ValInvData.FieldId != 11062
  9.   AND ValInvData.FieldId != 11055
  10.   AND ValInvData.FieldId != 11228
  11.   AND ValInvData.FieldId != 11230
  12.   AND ValInvData.FieldId != 14676;

示例2（不起作用）：

  1. UPDATE ValInvData
  2. SET ValStr = (SELECT ValStr
  3.               FROM ValInvData
  4.               WHERE Valdata.FieldId = 11061
  5.                 AND ObjData.ObjID = ValData.ObjId
  6.                 AND ValInvData.ObjInv = ValInvData.ObjInv
  7.                 AND ValInvData.FieldId != 11051
  8.                 AND ValInvData.FieldId != 11061
  9.                 AND ValInvData.FieldId != 11062
  10.                 AND ValInvData.FieldId != 11055
  11.                 AND ValInvData.FieldId != 11228
  12.                 AND ValInvData.FieldId != 11230
  13.                 AND ValInvData.FieldId != 14676);

最终解决方案：

  1. update ValInvData
  2. set ValStr = coalesce(
  3.     (
  4.         select min(ValStr) -- 保证只返回一个值，
  5.                             -- 可能最好抛出错误??
  6.         from ValInvData as vid
  7.         where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv and vid.FieldId = 11061
  8.     ),
  9.     ValInvData.ValStr) -- 也可以使用exists子查询来确保匹配行可用
  10. 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:

  1. Table ID  Field ID  Obj ID  Obj Inv  example_Date
  2. --------------------------------------------------
  3. 1         2         1       3        01/01/1996
  4. 1         4         1       3        01/02/1996
  5. 1         6         1       3        01/01/1996
  6. 1         2         2       4        01/05/1996
  7. 1         4         2       4        01/01/1997
  8. 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:

  1. Obj ID = Obj ID
  2. Obj Inv = Obj Inv
  3. Field ID = 6

And the result would look like the below ( * = the updated data)

Table1:

  1. Table ID  Field ID  Obj ID  Obj Inv  example_Date
  2. -------------------------------------------------
  3. 1         2         1       3        01/01/1996
  4. 1         4         1       3        01/02/1996
  5. 1         6         1       3       *01/01/1996
  6. 1         2         2       4        01/05/1996
  7. 1         4         2       4        01/01/1997
  8. 1         6         2       4       *01/05/1996

Any help would be highly appreciated!

Example 1 (does not work):

  1. UPDATE ValInvData
  2. SET ValStr = ValStr
  3. WHERE ValInvData.FieldId = 11061
  4.   AND ValInvData.ObjID = ValInvData.ObjId
  5.   AND ValInvData.ObjInv = ValInvData.ObjInv
  6.   AND ValInvData.FieldId != 11051
  7.   AND ValInvData.FieldId != 11061
  8.   AND ValInvData.FieldId != 11062
  9.   AND ValInvData.FieldId != 11055
  10.   AND ValInvData.FieldId != 11228
  11.   AND ValInvData.FieldId != 11230
  12.   AND ValInvData.FieldId != 14676;

Example 2 (does not work):

  1. UPDATE ValInvData
  2. SET ValStr = (SELECT ValStr
  3.               FROM ValInvData
  4.               WHERE Valdata.FieldId = 11061
  5.                 AND ObjData.ObjID = ValData.ObjId
  6.                 AND ValInvData.ObjInv = ValInvData.ObjInv
  7.                 AND ValInvData.FieldId != 11051
  8.                 AND ValInvData.FieldId != 11061
  9.                 AND ValInvData.FieldId != 11062
  10.                 AND ValInvData.FieldId != 11055
  11.                 AND ValInvData.FieldId != 11228
  12.                 AND ValInvData.FieldId != 11230
  13.                 AND ValInvData.FieldId != 14676);

Final Solution

  1. update ValInvData
  2. set ValStr = coalesce(
  3.     (
  4.         select min(ValStr) -- to guarantee only one value returned,
  5.                                  -- possibly better to error??
  6.         from ValInvData as vid
  7.         where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv and vid.FieldId = 11061
  8.     ),
  9.     ValInvData.ValStr) -- could also use exists subquery to ensure matching row is available
  10. 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;

英文: 
  1. update ValInvData
  2. set example_date = coalesce(
  3.     (
  4.         select min(example_date) -- to guarantee only one value returned,
  5.                                  -- possibly better to error??
  6.         from ValInvData as vid
  7.         where vid.ObjId = ValInvData.ObjId and vid.ObjInv = ValInvData.ObjInv
  8.             and vid.FieldId = 2
  9.     ),
  10.     example_date) -- could also use exists subquery to ensure matching row is available
  11. where FieldId = 6;

huangapple
  • 本文由 发表于 2023年7月11日 04:07:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/76657004.html
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定