insert into a select * from a(页 1) - 内存数据库 - ITPUB论坛

2008-5-5 13:16 jidongzheng

insert into a select * from a

TimeTen不支持直接从一个表向自己插入记录. 好奇怪.

Command> insert into a select * from a;
2377: Target table of insert cannot be in the from clause
The command failed.

2008-5-5 14:11 tom_111

怎么是表都是a啊？这样可以吗？

2008-5-5 14:19 tom_111

altibase内存数据库产品支持此写法，下面是自己实验的结果。
iSQL> desc t_2;
[ TABLESPACE : SYS_TBS_MEMORY ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                  TYPE                      IS NULL
------------------------------------------------------------------------------
I1                                     VARCHAR(100) FIXED
I2                                     INTEGER       FIXED
I3                                     FLOAT          FIXED
T_2 have no index
T_2 have no primary key
iSQL> insert into t_2 values('www',12,12.00)
2 ;
1 row inserted.
iSQL> select * from t_2;
T_2.I1
--------------------------------------------------------------------------------------------------------
T_2.I2    T_2.I3
---------------------------
g1
1          1.1
g2
2          2.2
g3
3          3.3
www
12       12
4 rows selected.
iSQL> set linesize 800
iSQL> set colsize 80
iSQL> select * from t_2;
T_2.I1                                                                         T_2.I2    T_2.I3
--------------------------------------------------------------------------------------------------------------
g1                                                                               1          1.1
g2                                                                               2          2.2
g3                                                                               3          3.3
www                                                                            12       12
4 rows selected.
iSQL> insert into t_4 select * from t_2;
4 rows inserted.
iSQL> select * from t_4;
T_4.I1                                                                         T_4.I2    T_4.I3
--------------------------------------------------------------------------------------------------------------
g1                                                                               1          1.1
g2                                                                               2          2.2
g3                                                                               3          3.3
www                                                                            12       12
4 rows selected.
iSQL>

2008-5-5 15:30 jidongzheng

在一般的数据库,oracle,sqlServer............基于磁盘的.

都是没有问题的.

所以timeTen在很多方面还不完善.

只能通过先创建一个相同的表create table a_bak as select * from a;
再:
insert into a select * from a_bak;

2008-5-5 15:34 jidongzheng

[quote]原帖由 [i]tom_111[/i] 于 2008-5-5 14:19 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10278867&ptid=982195][img]http://www.itpub.net/images/common/back.gif[/img][/url]
altibase内存数据库产品支持此写法，下面是自己实验的结果。
iSQL> desc t_2;
[ TABLESPACE : SYS_TBS_MEMORY ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                  TYPE                      IS NULL
------------------------------------------------------------------------------
I1                                     VARCHAR(100) FIXED
I2                                     INTEGER       FIXED
I3                                     FLOAT          FIXED
T_2 have no index
T_2 have no primary key
iSQL> insert into t_2 values('www',12,12.00)
2 ;
1 row inserted.
iSQL> select * from t_2;
T_2.I1
--------------------------------------------------------------------------------------------------------
T_2.I2    T_2.I3
---------------------------
g1
1          1.1
g2
2          2.2
g3
3          3.3
www
12       12
4 rows selected.
iSQL> set linesize 800
iSQL> set colsize 80
iSQL> select * from t_2;
T_2.I1                                                                         T_2.I2    T_2.I3
--------------------------------------------------------------------------------------------------------------
g1                                                                               1          1.1
g2                                                                               2          2.2
g3                                                                               3          3.3
www                                                                            12       12
4 rows selected.
iSQL> insert into t_4 select * from t_2;
4 rows inserted.
iSQL> select * from t_4;
T_4.I1                                                                         T_4.I2    T_4.I3
--------------------------------------------------------------------------------------------------------------
g1                                                                               1          1.1
g2                                                                               2          2.2
g3                                                                               3          3.3
www                                                                            12       12
4 rows selected.
iSQL> [/quote]

楼上的写错了吧.
insert into t_4 select * from t_2;
t_2,而不是t_4才是我的本意

2008-5-5 18:50 tom_111

不好意思!ALTIBASE内存数据库可以支持此写法.
iSQL> select * from t_3;
T_3.ID0    T_3.ID1
------------------------
1          fff
1          fff
2 rows selected.
iSQL> insert into t_3 select * from t_3;
2 rows inserted.
iSQL> select * from t_3;
T_3.ID0    T_3.ID1
------------------------
1          fff
1          fff
1          fff
1          fff
4 rows selected.

2008-5-6 13:44 jidongzheng

是的.强.

2008-5-6 15:25 liyongdong

不错，tt不支持，是因为它不支持MVCC,而ALTIBASE 支持,是因为它支持MVCC.

2008-7-1 12:12 DBALover

学习!