精华内容
下载资源
问答
  • I'm trying to update a set of records (boolean fields) in a single query if possible.The input is coming from paginated radio controls, so a given POST will have the page's worth of IDs with a true or...

    I'm trying to update a set of records (boolean fields) in a single query if possible.

    The input is coming from paginated radio controls, so a given POST will have the page's worth of IDs with a true or false value.

    I was trying to go this direction:

    UPDATE my_table

    SET field = CASE

    WHEN id IN (/* true ids */) THEN TRUE

    WHEN id IN (/* false ids */) THEN FALSE

    END

    But this resulted in the "true id" rows being updated to true, and ALL other rows were updated to false.

    I assume I've made some gross syntactical error, or perhaps that I'm approaching this incorrectly.

    Any thoughts on a solution?

    解决方案

    Didn't you forget to do an "ELSE" in the case statement?

    UPDATE my_table

    SET field = CASE

    WHEN id IN (/* true ids */) THEN TRUE

    WHEN id IN (/* false ids */) THEN FALSE

    ELSE field=field

    END

    Without the ELSE, I assume the evaluation chain stops at the last WHEN and executes that update. Also, you are not limiting the rows that you are trying to update; if you don't do the ELSE you should at least tell the update to only update the rows you want and not all the rows (as you are doing). Look at the WHERE clause below:

    UPDATE my_table

    SET field = CASE

    WHEN id IN (/* true ids */) THEN TRUE

    WHEN id IN (/* false ids */) THEN FALSE

    END

    WHERE id in (true ids + false_ids)

    展开全文
  • update 和case when 使用

    2021-11-03 11:25:42
    UPDATE A SET amount =( CASE WHEN orders = ‘NO1’ THEN 1 WHEN orders = ‘NO2’ THEN 2 ELSE amount END )

    UPDATE A
    SET amount =(

    CASE
    WHEN orders = ‘NO1’ THEN
    1
    WHEN orders = ‘NO2’ THEN
    2
    ELSE amount
    END )

    展开全文
  • mysql> delimiter //mysql> UPDATE `sss` SET-> `sscore` =-> CASE-> WHEN `saddress` LIKE '%北京%'-> THEN (`sscore` + 1)-> WHEN `saddress` LIKE '%河南%'-&g...

    mysql> delimiter //

    mysql> UPDATE `sss` SET

    -> `sscore` =

    -> CASE

    -> WHEN `saddress` LIKE '%北京%'

    -> THEN (`sscore` + 1)

    -> WHEN `saddress` LIKE '%河南%'

    -> THEN (`sscore` + 2)

    -> WHEN `saddress` LIKE '%上海%'

    -> THEN (`sscore` + 5)

    -> END

    -> WHERE ('sscore' >= 30) AND ('sscore' < 60)//

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    Rows matched: 0 Changed: 0 Warnings: 1

    mysql> delimiter ;

    mysql>

    mysql> SHOW WARNINGS;

    +---------+------+--------------------------------------------+

    | Level | Code | Message |

    +---------+------+--------------------------------------------+

    | Warning | 1292 | Truncated incorrect DOUBLE value: 'sscore' |

    +---------+------+--------------------------------------------+

    1 row in set (0.00 sec)

    数据没有变啊?

    展开全文
  • 在处理批量更新某些数据的时候,如果跟你更新的字段的值都一样,比如某个状态都更新为某个固定值,直接用update table set xxx=xxx where xxx=xxx 这种即可如果要更新的字段的值是不固定的,用下面的update case when...

    在处理批量更新某些数据的时候,如果跟你更新的字段的值都一样,比如某个状态都更新为某个固定值,

    直接用update table set xxx=xxx where xxx=xxx 这种即可

    如果要更新的字段的值是不固定的,用下面的update case when where 这种方式就更方便了

    UPDATE tablename

    set a1= CASE userid

    WHEN 1 THEN a1+5

    WHEN 2 THEN a1+2

    END,

    a2= CASE userid

    WHEN 1 THEN a2-5

    WHEN 2 THEN a2-2

    END

    where userid in (1,2)

    用update case when 更新的时候一定要带上 where 条件,否则会更新整张表,后果很严重。

    补充:Mysql update && case when 联合使用 批量更新

    直接码sql:

    room字段值为 18F-N01 这种格式

    UPDATE t_report SET room = CONCAT_WS( '-', SUBSTRING_INDEX(room, '-', 1) ,

    CASE SUBSTRING_INDEX(room, '-', -1)

    WHEN 'N01' THEN 'N02'

    WHEN 'N02' THEN 'N01'

    WHEN 'N03' THEN 'N11'

    WHEN 'N04' THEN 'N10'

    WHEN 'N05' THEN 'N09'

    WHEN 'N06' THEN 'N08'

    WHEN 'N07' THEN 'N07'

    WHEN 'N08' THEN 'N06'

    WHEN 'N09' THEN 'N05'

    WHEN 'N10' THEN 'N03'

    END )

    WHERE rid IN (

    SELECT rid FROM t_report WHERE rdate = '190306' AND ordinal BETWEEN '23' AND '32'

    )

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持站圈网。如有错误或未考虑完全的地方,望不吝赐教。

    展开全文
  • update与when一起使用 UPDATE A SET asset_status =( CASE WHEN outType = ‘1’ THEN ...当updatecase when 与left join 一起使用 update hard_asset left join hard_storage_out_asset on hard_asset.as
  • UPDATE sys_user SET user_id = CASE user_id WHEN '86' THEN '301111' WHEN '88' THEN '301112' WHEN '124' THEN '301113' WHEN '125' THEN '301114' END WHERE `name` IN ( '姜玲玲'...
  • Set / Case When value Like

    2021-03-14 14:37:17
    Until now I updated the value field on my database like this :'UPDATE lignefraishorsforfait '. 'SET lignefraishorsforfait.libelle = CONCAT("REFUSE : ", libelle) 'However, I would like to make a check ...
  • case when 条件1 then 取值1 when 条件2 then 取值2 else 取值3 endwhen后...语句:下面把id=12 customfield_id = 1记录中的def_val值更新为1,同时把其它的都更新为2UPDATE mantis_customfield_values SET def_val ...
  • update中使用case when

    2021-11-26 09:46:01
    update中使用case when ...update CZZCSBMXBT set htid = CASE WHEN htid is null then 'OD0UOKP3U9AYY5ZOOTJLKXQYNMURFRBQ'when instr(htid, 'OD0UOKP3U9AYY5ZOOTJLKXQYNMURFRBQ') > 0 then htid E
  • 欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入 1. 测试一 create table test(id int, tag int, num int); insert into test (id, tag, num) values(1... update test set tag = 4, num=case when ta...
  • # case when then else end 在 update 语句 中的使用 select * from boys; # +--+------+---+----+-------+-------------------+----+---------------+ # |id|name |sex|age |address|created_time |enum|create_...
  • mysql执行顺序binlog的生成顺序一致java代码中调用jdbc如下次序更新数据库,Tx.beginSQL1SQL2SQL3Tx.end那么生成的binlog还是SQL1、SQL2、SQL3执行顺序但是在含有case when update语句中,生成的binlog顺序,...
  • UPDATE mytable SET myfield = CASE id WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' END WHERE id IN (1,2,3); 这里使用了case when 这个小技巧来实现批量更新。 举个例子: 代码如下: ...
  • MYSQL:case when用法

    千次阅读 2021-01-30 00:56:17
    简单Case函数和Case搜索函数。第一种 格式 :简单Case函数 :格式说明case 列名when 条件值1 then 选项1when 条件值2 then 选项2.......else 默认值 endeg:selectcase job_levelwhen '1' then ...
  • 场景是这样的:有多条记录需要更新,但是每一条记录更新的值都不同,这时候最简单的方式就是单独更新每一条记录。... update order_item set num_value = <foreach collection="list" item="item" index="inde
  • 问题引入 ...UPDATE tab CASE creator WHEN '1' THEN SET '2' END CASE CASE operator WHEN '5' THEN SET '6' END CASE WHERE id = '10' 正确的解决方案 UPDATE tab SET creator = CA
  • 示例场景: (1) 数据表... 示例语句: update table_001 set column_01 = (case when column_10=0 then 0 when column_10<120 then 1 when column_10>=120 and column_10<140 then 2 when column_10>=140 and column_10...
  • update 结合 case when

    2021-10-14 15:48:18
    UPDATE mytable SET myfield = CASE id WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' END WHERE id IN (1,2,3)
  • --实战使用 还做了时间转换 UPDATE bs_version v JOIN bs_test t ON v.id = t.`version_id` SET v.plan_release_time = STR_TO_DATE( CASE WHEN t.`plan_time` = 'NULL' THEN NULL ELSE t.`plan_time` END, '%Y/%m/...
  • 在日常开发中由于业务逻辑较为复杂,常常需要用到UPDATE和CASE...WHEN...THEN...ELSE...END一起做一些复杂的更新。有时候因为对这几个字句理解得不透彻会带来很大的困扰。因此对UPDATE和CASE WHEN结构的特性做进一步...
  • With MYSQL I'm using this query:UPDATE CustomerDetails_COPYSET Category_ID = 10WHERE Category_ID = 2Thats fine but I'd like to ad 15+ more SET/WHERE to it like:UPDATE CustomerDetails_COPYSET Category_...
  • msyql性能优化之update

    千次阅读 2021-01-19 13:13:02
    我们都知道在MySQL中批量insert的速度会比一条条insert快很多,在MySQL中批量...批量更新MySQL更新语句很简单,更新一条数据的某个字段,一般这样写:UPDATE table SET field = 'value' WHERE other_field = 'othe...
  • 另外一个示例:交换两条记录中的字段值方法 表test: id priority 1 1 2 2 方法一: mysql语句如下:update question set sort=(case when id=7 then (select a.sort from (select tmp.* from ...
  • 第一种: updatesalary setsex= (casesex when'm'then 'f' else 'm' end); 第二种 update salary t set t.sex = case when t.sex = 'm' then 'f' e...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 205,926
精华内容 82,370
关键字:

set和caseupdate