Update statement with different fixed, non-persisted values-CodePudding

I have following SQL statement (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')

The hardcoded number values should be set with fixed predefined values (that are not in any table, just excel) depending on their ape.code and the param_name.

Of course I could just do this statement for each ape.code and param_name combination, but that's quite tedious. What's the most efficient and time-saving way to do this in SQL?

CodePudding user response：

You need to create a conversion table. It is easy to create insert statements in excel using something like

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

You then pull down for all the lines and paste it into a query pane.

CodePudding user response：

You can use a single MERGE statement and include all the data within the query:

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;