update a,b set a.date =add_months(b.date1,3) where a.id=b.id
--前提是你A,B表中日期都是date型的 --如果不是需要转换成date型 update tab_a a set a.id = (select add_months(b.date1,3) from tab_b b where a.id=b.id) where exists (select 1 from tab_a c where c.id=a.id)
上面写错了,是A.DATE update tab_a a set A.DATE = (select add_months(b.date1,3) from tab_b b where a.id=b.id) where exists (select 1 from tab_a c where c.id=a.id)
update a set a.date =add_months((select b.date1 from b where a.id=b.id),3)
UPDATE A SET DATE = (SELECT (DATE1 + 90) FROM B WHERE A.ID = B.ID)
update a set a.date =add_months((select b.date1 from b where a.id=b.id),3)
--怎么不行?我这可以啊?Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select * from tab_a; ID DATE1 ---------- ---------- 1 2 3SQL> select * from tab_b; ID DATE2 ---------- ---------- 1 2010-01-01 2 2010-02-09SQL> update tab_a a set A.date1 = (select add_months(b.date2,3) from tab_b b where a.id=b.id) 2 where exists (select 1 from tab_a c where c.id=a.id) 3 ;3 rows updated.SQL> select * from tab_a; ID DATE1 ---------- ---------- 1 2010-04-01 2 2010-05-09 3SQL>
--上面我有个地方写错了,exists条件里要用tab_bSQL> select * from tab_a; ID DATE1 ---------- ---------- 1 2 3 2010-12-14SQL> select * from tab_b; ID DATE2 ---------- ---------- 1 2010-01-01 2 2010-02-09SQL> update tab_a a set A.date1 = (select add_months(b.date2,3) from tab_b b where a.id=b.id) 2 where exists (select 1 from tab_b c where c.id=a.id); 2 rows updated.SQL> select * from tab_a; ID DATE1 ---------- ---------- 1 2010-04-01 2 2010-05-09 3 2010-12-14SQL>
ORA-01427: 单行子查询返回多个行 我的怎么出现这个啊? 我和你一样的啊?
update t6 a set A.date2 = (select add_months(b.date1,3) from t7 b where a.id=b.id) where exists (select 1 from t7 c where c.id=a.id)ORA-01427: 单行子查询返回多个行
那你肯定A表ID对应B表多笔数据 这条数据你会返回多个:select add_months(b.date1,3) from t7 b where a.id=b.id看你怎么取了,取第一个直接用rownum=1就行 select add_months(b.date1,3) from t7 b where a.id=b.id and rownum=1
--B表的日期字段 SQL> select DATETI from yyq_date where id=1;DATETI --------------------------------------------------------------------------- 11-10-01 17:17:17.000000--A表的日期字段 SQL> select DATETI from yyq_date2 where id=1;DATETI --------------------------------------------------------------------------- 12-08-01 17:17:17.000000--更新A表的日期=B表的日期+3个月 SQL> update yyq_date2 d2 set d2.DATETI = (select add_months(d1.DATETI,3) from y yq_date d1 where d1.ID = d2.ID);1行更新。--再检索A表的日期字段=B表的日期+3个月 SQL> select DATETI from yyq_date2 where id=1;DATETI --------------------------------------------------------------------------- 12-01-01 17:17:17.000000SQL>
--前提是你A,B表中日期都是date型的
--如果不是需要转换成date型
update tab_a a set a.id = (select add_months(b.date1,3) from tab_b b where a.id=b.id)
where exists (select 1 from tab_a c where c.id=a.id)
update tab_a a set A.DATE = (select add_months(b.date1,3) from tab_b b where a.id=b.id)
where exists (select 1 from tab_a c where c.id=a.id)
--怎么不行?我这可以啊?Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> alter session set nls_date_format='yyyy-mm-dd';Session altered.SQL> select * from tab_a; ID DATE1
---------- ----------
1
2
3SQL> select * from tab_b; ID DATE2
---------- ----------
1 2010-01-01
2 2010-02-09SQL> update tab_a a set A.date1 = (select add_months(b.date2,3) from tab_b b where a.id=b.id)
2 where exists (select 1 from tab_a c where c.id=a.id)
3 ;3 rows updated.SQL> select * from tab_a; ID DATE1
---------- ----------
1 2010-04-01
2 2010-05-09
3SQL>
--上面我有个地方写错了,exists条件里要用tab_bSQL> select * from tab_a; ID DATE1
---------- ----------
1
2
3 2010-12-14SQL> select * from tab_b; ID DATE2
---------- ----------
1 2010-01-01
2 2010-02-09SQL> update tab_a a set A.date1 = (select add_months(b.date2,3) from tab_b b where a.id=b.id)
2 where exists (select 1 from tab_b c where c.id=a.id); 2 rows updated.SQL> select * from tab_a; ID DATE1
---------- ----------
1 2010-04-01
2 2010-05-09
3 2010-12-14SQL>
我的怎么出现这个啊? 我和你一样的啊?
where exists (select 1 from t7 c where c.id=a.id)ORA-01427: 单行子查询返回多个行
这条数据你会返回多个:select add_months(b.date1,3) from t7 b where a.id=b.id看你怎么取了,取第一个直接用rownum=1就行
select add_months(b.date1,3) from t7 b where a.id=b.id and rownum=1
--B表的日期字段
SQL> select DATETI from yyq_date where id=1;DATETI
---------------------------------------------------------------------------
11-10-01 17:17:17.000000--A表的日期字段
SQL> select DATETI from yyq_date2 where id=1;DATETI
---------------------------------------------------------------------------
12-08-01 17:17:17.000000--更新A表的日期=B表的日期+3个月
SQL> update yyq_date2 d2 set d2.DATETI = (select add_months(d1.DATETI,3) from y
yq_date d1 where d1.ID = d2.ID);1行更新。--再检索A表的日期字段=B表的日期+3个月
SQL> select DATETI from yyq_date2 where id=1;DATETI
---------------------------------------------------------------------------
12-01-01 17:17:17.000000SQL>