b***i
发帖数: 3043 | 1 比如列叫column，必须满足column > 10，由于unique, 不能直接加，因为会暂时重复
怎么做呢？ |
c*****d
发帖数: 6045 | 2 update table_name
set column_1 = column_1 + 1
【在 b***i 的大作中提到】
: 比如列叫column，必须满足column > 10，由于unique, 不能直接加，因为会暂时重复
: 怎么做呢？
|
r**********d
发帖数: 510 | 3 the column should be processed desc.
probably can do it in a stored proc.
for each
select *
from t
order by col desc
loop
if > 10 then
update
end if
end loop
if new col doesnt have to be old col + 1, you can update set col = next
value of the sequence.
|
c*****d
发帖数: 6045 | 4 tom kyte说过，能用一句sql完成的，永远不要用cursor或者stored procedure
这个用一个update就搞定了
【在 r**********d 的大作中提到】
: the column should be processed desc.
: probably can do it in a stored proc.
: for each
: select *
: from t
: order by col desc
: loop
: if > 10 then
: update
: end if
|
c*****d
发帖数: 6045 | 5 to: bihai & rslgreencard
SQL> select * from v$version;
BANNER
------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5 rows selected.
SQL> create table coolbid (id number unique);
Table created.
SQL> insert into coolbid values (1);
1 row created.
SQL> insert into coolbid values (2);
1 row created.
SQL> insert into coolbid values (3);
1 row created.
SQL> insert into coolbid values (3);
insert into coolbid values (3)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C009453) violated
SQL> select * from coolbid;
ID
----------
1
2
3
3 rows selected.
SQL> update coolbid set id=id+1;
3 rows updated.
SQL> select * from coolbid;
ID
----------
2
3
4
3 rows selected. |
r**********d
发帖数: 510 | 6 select @@version;
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 (Build
7601: Service Pack 1)
CREATE TABLE coolbid
(
ID INT UNIQUE
);
GO
INSERT INTO coolbid
VALUES (1),
(2),
(3);
GO
(3 row(s) affected)
INSERT INTO coolbid
VALUES (1);
GO
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__coolbid__3214EC26654E5167'. Cannot
insert duplicate key in object 'dbo.coolbid'. The duplicate key value is (1).
The statement has been terminated.
SELECT * FROM coolbid;
1
2
3
UPDATE coolbid
set id = id + 1;
works in sql server.
indie=> select version();
version
----------------------------------------------------------------------------
---------------------------
PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (
Debian 4.4.5-8) 4.4.5, 64-bit
(1 row)
indie=> CREATE TABLE coolbid
indie-> (
indie(> ID INT UNIQUE
indie(> );
NOTICE: CREATE TABLE / UNIQUE will create implicit index "coolbid_id_key"
for table "coolbid"
CREATE TABLE
indie=>
indie=> INSERT INTO coolbid
indie-> VALUES (1),
indie-> (2),
indie-> (3);
INSERT 0 3
indie=>
indie=> INSERT INTO coolbid
indie-> VALUES (1);
ERROR: duplicate key value violates unique constraint "coolbid_id_key"
DETAIL: Key (id)=(1) already exists.
indie=>
indie=> SELECT * FROM coolbid;
id
----
1
2
3
(3 rows)
indie=>
indie=> UPDATE coolbid
indie-> set id = id + 1;
ERROR: duplicate key value violates unique constraint "coolbid_id_key"
DETAIL: Key (id)=(2) already exists.
doesnt work in postgres.
thanks for the posting. I learned something new today. |
B*****g
发帖数: 34098 | 7 大师讲一讲里面的机制吧
Production
【在 c*****d 的大作中提到】
: to: bihai & rslgreencard
: SQL> select * from v$version;
: BANNER
: ------------------------------------------------------------------
: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
: PL/SQL Release 11.2.0.4.0 - Production
: CORE 11.2.0.4.0 Production
: TNS for Linux: Version 11.2.0.4.0 - Production
: NLSRTL Version 11.2.0.4.0 - Production
: 5 rows selected.
|
