dsfdsfsdf45489 2018-07-03 15:35
浏览 81
已采纳

更新查询更改多列中的值

How can I write a query to change a value in multiple columns where there is a specific value? I want to update a table: look through 40 columns (q1-q40) if the value in that column is -1 then change that to -999 for all rows. Do i still have to list cases for each column or is there an easier way?

Thanks!

  • 写回答

2条回答 默认 最新

  • dougang6821 2018-07-03 15:49
    关注

    You can't escape the long winded approach.

    If I understand your requirement, it would be something like...

    UPDATE
      table
    SET
      col00 = CASE WHEN col00 = -1 THEN -999 ELSE col00 END,
      col01 = CASE WHEN col01 = -1 THEN -999 ELSE col01 END,
      col02 = CASE WHEN col02 = -1 THEN -999 ELSE col02 END,
      ...
      col37 = CASE WHEN col37 = -1 THEN -999 ELSE col37 END,
      col38 = CASE WHEN col38 = -1 THEN -999 ELSE col38 END,
      col39 = CASE WHEN col39 = -1 THEN -999 ELSE col39 END
    WHERE
      -1 IN (col00, col01, col02, ... col37, col38, col39)
    

    Or 40 straight forward updates...

    This is a good example of when code that writes SQL is a useful approach.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 请问一下这个运行结果是怎么来的
  • ¥15 这个复选框什么作用?
  • ¥15 单通道放大电路的工作原理
  • ¥30 YOLO检测微调结果p为1
  • ¥20 求快手直播间榜单匿名采集ID用户名简单能学会的
  • ¥15 DS18B20内部ADC模数转换器
  • ¥15 做个有关计算的小程序
  • ¥15 MPI读取tif文件无法正常给各进程分配路径
  • ¥15 如何用MATLAB实现以下三个公式(有相互嵌套)
  • ¥30 关于#算法#的问题:运用EViews第九版本进行一系列计量经济学的时间数列数据回归分析预测问题 求各位帮我解答一下