关闭

update更新多个字段

98人阅读 评论(0) 收藏 举报
分类:

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$') 

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1882次
    • 积分:75
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:0篇
    • 译文:1篇
    • 评论:0条
    文章分类
    文章存档