积跬步 致千里

使人愉快的劳动,能医治心灵的创伤。

update更新多个字段

oracle可执行数据库语句:

UPDATE yl_lnrxx 
   SET (lbtid, lsfxs, lksrq, ljsrq, lfwzjid, lfwzjmc) =  
   (select x_id, 1, xksxsrq, xjsxsrq, xfwzjid, xfwzj from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  
 WHERE pk_yl_lnrxx = (select xlnrid from btjs_xszffwbt where x_id  = '$pkid$')

但在mysql无法识别,需要转换语法

尝试mysql语法:

 UPDATE yl_lnrxx a, (select x_id, 1 AS sfxs,xksxsrq, xjsxsrq, xfwzjid, xfwzj from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  b 
 SET a.lbtid = b.x_id, a.lsfxs = b.sfxs,a.lksrq =b.xksxsrq,a.ljsrq=b.xjsxsrq,a.lfwzjid =b.xfwzjid,a.lfwzjmc=b.xfwzj 
 WHERE pk_yl_lnrxx = (select xlnrid from btjs_xszffwbt where x_id  = '$pkid$')

mysql可行,oracle报错:missing SET  keyword 
oracle调试无解

单个单个获取,两者能通用,但是语句多,繁琐。
UPDATE yl_lnrxx 
 SET lbtid =  (select x_id from  (select x_id from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  xx),
     lsfxs =  1,
     lksrq =  (select xksxsrq from  (select xksxsrq from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  xx),
     ljsrq =  (select xjsxsrq from  (select xjsxsrq from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  xx),
     lfwzjid =  (select xfwzjid from  (select xfwzjid from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  xx),
     lfwzjmc =  (select xfwzj from  (select xfwzj from btjs_xszffwbt  where x_id = '$pkid$' AND sys_spzt = 1 AND sys_djzt = '1')  xx)
 WHERE pk_yl_lnrxx = (select xlnrid from btjs_xszffwbt where x_id  = '$pkid$') 

需要有更好的方式处理
写类,让它们规范的转化,而不是直接用数据库语句让它们转化

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yushanamina/article/details/51544003
个人分类: Oracle sql Mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

update更新多个字段

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭