高手，我有一个update语句问题请教，急啊，谢谢

Question

没看懂楼主的意思!
不过我用过个种写法!不知道是否和楼主想的东东一样:
UPDATE T1 A SET (A.NAME,A.AGE) = (SELECT B.NAME,B.AGE FROM T2 B WHERE B.ID = A.ID)
后面还可以加上对T1表的WHERE更新条件!

Answer 1

补充:当然括号里的SELECT ... FROM 后也可以加多张表!

Answer 2

可以用
buhuiyang(杨言不悔)说的这种格式.

Answer 3

各位,这种格式是正确的,可问题现在是,我查找出来的值,即SELECT B.NAME,有值的话给赋值,
没有值的话,原来的值全部清空,比如原来是5 ,查处来 B.NAME=6可以更新,则A.NAME=6,其他不符合条件的,原来值是3,全部清为空!代码如下,请各位帮忙看看.谢谢上面的两位!
update db_code_build a set cur_no = 
(SELECT b.ch_value
                     FROM (select ch_value, ch_flag
                             from (select max(dj_no) as ch_value, ch_flag
                                     from (select *
                                             from (select RECEIPTS_ID ||
                                                          station_id || cur_month as ch_flag
                                                     from DB_CODE_BUILD)) u,
                                          (select *
                                             from (select substr(CH_ID, 1, 13) as DJ_ID,
                                                          substr(CH_ID, 14, 5) as dj_no
                                                     from YW_TRAIN_CHANGE)) T
                                    where u.ch_flag = T.dj_id
                                    group by ch_flag) tempTable,
                                  DB_CODE_BUILD
                            where tempTable.ch_value > db_code_build.cur_no
                              and substr(ch_flag, 1, 2) =
                                  DB_CODE_BUILD.receipts_id
                              and substr(ch_flag, 3, 5) =
                                  DB_CODE_BUILD.station_id
                              and substr(ch_flag, 8, 6) =
                                  DB_CODE_BUILD.cur_month) b
                                  where a.receipts_id||a.station_id||a.cur_month = b.ch_flag)

Answer 4

是要加一个判空的函数吗? nvl 就可以啊update db_code_build a set cur_no = 
(SELECT nvl(b.ch_value,null)
                     FROM (select ch_value, ch_flag
                             from (select max(dj_no) as ch_value, ch_flag
                                     from (select *
                                             from (select RECEIPTS_ID ||
                                                          station_id || cur_month as ch_flag
                                                     from DB_CODE_BUILD)) u,
                                          (select *
                                             from (select substr(CH_ID, 1, 13) as DJ_ID,
                                                          substr(CH_ID, 14, 5) as dj_no
                                                     from YW_TRAIN_CHANGE)) T
                                    where u.ch_flag = T.dj_id
                                    group by ch_flag) tempTable,
                                  DB_CODE_BUILD
                            where tempTable.ch_value > db_code_build.cur_no
                              and substr(ch_flag, 1, 2) =
                                  DB_CODE_BUILD.receipts_id
                              and substr(ch_flag, 3, 5) =
                                  DB_CODE_BUILD.station_id
                              and substr(ch_flag, 8, 6) =
                                  DB_CODE_BUILD.cur_month) b
                                  where a.receipts_id||a.station_id||a.cur_month = b.ch_flag)

Answer 5

不过UPDATE T1 A SET (A.NAME,A.AGE) = (SELECT B.NAME,B.AGE FROM T2 B WHERE B.ID = A.ID)
如果select得到多条记录的话,就会有问题的.
这样就要用批量更新MERGE 可以用来搞定

Answer 6

你单独执行SELECT nvl(b.ch_value,null)... 是否有值,查询是否能通过.如果子查询查出是多条记录肯定会有问题的, 就是你的SELECT b.ch_value 一定要唯一, 否则没有办法更新,他不知道用哪个值 更新,

Answer 7

可以更新的,自己解决了,我是这么写的,估计,再简单一些也可以,还是谢谢大家
update db_code_build a
   set cur_no = (
                 
                 SELECT b.ch_value
                   FROM (select ch_value, ch_flag
                            from (select max(dj_no) as ch_value, ch_flag
                                    from (select *
                                            from (select RECEIPTS_ID || station_id ||
                                                         cur_month as ch_flag
                                                    from DB_CODE_BUILD)) u,
                                         (select *
                                            from (select substr(CH_ID, 1, 13) as DJ_ID,
                                                         substr(CH_ID, 14, 5) as dj_no
                                                    from YW_TRAIN_CHANGE)) T
                                   where u.ch_flag = T.dj_id
                                   group by ch_flag) tempTable,
                                 DB_CODE_BUILD
                           where tempTable.ch_value > db_code_build.cur_no
                             and substr(ch_flag, 1, 2) =
                                 DB_CODE_BUILD.receipts_id
                             and substr(ch_flag, 3, 5) =
                                 DB_CODE_BUILD.station_id
                             and substr(ch_flag, 8, 6) = DB_CODE_BUILD.cur_month) b
                  where a.receipts_id || a.station_id || a.cur_month =
                        b.ch_flag)
 where a.receipts_id || a.station_id || a.cur_month in
       (select c.ch_flag
          from (select ch_value, ch_flag
                  from (select max(dj_no) as ch_value, ch_flag
                          from (select *
                                  from (select RECEIPTS_ID || station_id ||
                                               cur_month as ch_flag
                                          from DB_CODE_BUILD)) u,
                               (select *
                                  from (select substr(CH_ID, 1, 13) as DJ_ID,
                                               substr(CH_ID, 14, 5) as dj_no
                                          from YW_TRAIN_CHANGE)) T
                         where u.ch_flag = T.dj_id
                         group by ch_flag) tempTable,
                       DB_CODE_BUILD
                 where tempTable.ch_value > db_code_build.cur_no
                   and substr(ch_flag, 1, 2) = DB_CODE_BUILD.receipts_id
                   and substr(ch_flag, 3, 5) = DB_CODE_BUILD.station_id
                   and substr(ch_flag, 8, 6) = DB_CODE_BUILD.cur_month) c)