使用不同的固定、非持久化值更新语句

Question

我有以下SQL语句(Oracle(：

UPDATE work x
SET param1= 200, length= 2.5, length_fixed= 0
WHERE param_name = 'pipe' 
AND x.id IN (SELECT y.id from work y
JOIN work_specific bsa ON y.foreign_id= bsa.id
JOIN work_plan ape ON  bsa.foreig_id = ape.id
WHERE ape.code = 'AB')

硬编码数值应根据其ape.code和param_name使用固定的预定义值(不在任何表中，仅在excel中(进行设置。

当然，我可以为每个ape.code和param_name组合做这个语句，但这相当乏味。在SQL中，最有效、最省时的方法是什么？

Answer 1

您需要创建一个转换表。使用之类的东西在excel中创建插入语句很容易

+ "insert into myTable (col1, col2, col3) values ('" & B1 a "','" & B2 & "','" & B3 & "');"

然后下拉所有行并将其粘贴到查询窗格中。

Answer 2

您可以使用单个MERGE语句并在查询中包含所有数据：

MERGE INTO work dst
USING (
WITH data (param1, length, length_fixed, param_name, code) AS (
SELECT 200, 2.5, 0, 'pipe',       'AB' FROM DUAL UNION ALL
SELECT 100, 1.0, 0, 'short_pipe', 'BC' FROM DUAL
)
SELECT r.ROWID AS rid,
d.*
FROM   data d
INNER JOIN work x
ON (x.param_name = d.param_name)
INNER JOIN (
work y
JOIN work_specific bsa
ON y.foreign_id= bsa.id
JOIN work_plan ape
ON bsa.foreig_id = ape.id
)
ON (x.id = ape.id AND d.code = ape.code)
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
UPDATE
SET param1 = src.param1,
length = src.length,
length_fixed = src.length_fixed;