如何在oracle sql中使用case语句更改2列值

I am looking to find out how I can change values in 2 columns based on a case when statement.

我想找出如何根据case语句更改2列中的值。

An example of the table data is below (live data has around 60 columns that I have bring in but only this column:

表数据的一个示例如下（实时数据有大约60列我已经引入但只有这一列：

QUEUE | NOTES
12345 | Lorem 
12345 | ipsum
45678 | dolor
78901 | sit
90123 | amet

Now I have written the following query that gives me the first part:

现在我编写了以下查询，它给出了第一部分：

SELECT
  CASE
    WHEN SUBSTR (QUEUE, 1, 2) = '12'
  THEN 'Abandoned'
  ELSE QUEUE
  END AS QUEUE,
  NOTES
FROM
  TABLE_1
;

The second step that I need to undertake is based on the first case statement. If the queue field has the queue abandoned then I need to replace the note field with text saying 'Queue Amended by User AN 29.09.16' but I am not sure how I go about doing this.

我需要采取的第二步是基于第一个案例陈述。如果队列字段已放弃队列，那么我需要将文本替换为“由用户AN推荐的队列AN 29.09.16”，但我不确定如何执行此操作。

I am guessing I could write it in a sub select query but again I don't know how I target if one column value equals something then replace another columns values. Also would like to know if its possible to write it all within one case statement...

我猜我可以在子选择查询中写它但我又不知道如果一个列值等于某个东西然后替换另一个列值我如何定位。还想知道是否可以在一个案例陈述中写出所有内容......

If somebody could please advise.

如果有人可以请提出建议。

Thanks

谢谢

3 个解决方案

#1

Use CASE Expression for Note column as well

也可以使用CASE Expression for Note列

SELECT
  CASE
     WHEN SUBSTR (QUEUE, 1, 2) = '12'
     THEN 'Abandoned'
     ELSE QUEUE
  END AS QUEUE,
  CASE
     WHEN SUBSTR (QUEUE, 1, 2) = '12'
     THEN 'Queue Amended by User AN 29.09.16'
     ELSE NOTES
  END  AS Notes
FROM
TABLE_1

#2

Assuming you do not actually want to update the underlying table_1 but only present the results then you just need to repeat the case statement for the second column:

假设您实际上并不想更新基础table_1但仅显示结果，那么您只需要重复第二列的case语句：

SELECT
  (CASE
    WHEN SUBSTR (QUEUE, 1, 2) = '12'
  THEN 'Abandoned'
  ELSE QUEUE
  END) AS QUEUE,

  (CASE
    WHEN SUBSTR (QUEUE, 1, 2) = '12'
  THEN 'Queue Amended by User ' ||user ||' '|| to_char(sysdate, 'DD.MM.RRRR')
  ELSE NOTES
  END) AS NOTES
FROM table_1

#3

If you have a table called table_1 with columns queue and notes with the current values in your sample input, and you must UPDATE the table as you described, it's easy (and you don't need a "case" expression or two, all you need is a WHERE filter):

如果你有一个名为table_1的表，其中列为队列，并且注释中包含示例输入中的当前值，并且你必须如你所描述的那样更新表，这很容易（并且你不需要一个或两个“case”表达式，所有你需要是一个WHERE过滤器）：

update table_1
   set queue = 'Abandoned',
       notes = 'Queue Amended by User AN 09.29.16'
where  queue like '12%'
;

(Or the value for NOTES can be concatenated with variables for User and/or Date as E. Ninis has shown in his/her solution.) Note that the "like" condition will allow Oracle to use an index you may have on the queue column; using substr() on it will not.

（或者NOTES的值可以与用户和/或日期的变量连接起来，如N. Ninis在他/她的解决方案中所示。）请注意，“like”条件将允许Oracle使用您在队列中可能拥有的索引柱;在它上面使用substr（）不会。

#1