SQL Server MySQL database modify the value of the self-added column and the solution of the corresponding problem _mssql

SQL Server platform modifies self-added column values

Since the migration of the SQL Server database was previously processed, a change to its own column value has been attempted, but modifying the self-added column value through an SQL statement is strictly disallowed and a direct error (Unable to update the identity column ' self-added column name '). SQL Server My tests are 2008, 2012, and 2014 and do not allow changes to the value of the column, and I believe that the SQL Server 2005+ environment does not allow changes to the field column values.

If you do not want to modify the values of your own columns on the SQL Server platform, manually add the properties of the column, and then modify the column value, and then manually append the properties of the column by adding the Columns property after the modification succeeds. If you modify the horizon in your build environment, it is recommended that you deal with this type of problem in idle time (after a short period of time, when the platform or Web site uses very few users). A large amount of data and multiple table associations, it is changed by T-SQL. The biggest disadvantage of this method is to manually assist in canceling and adding the properties of the self.

There is also a way to first modify the data into a T-SQL Insert script, and then delete the batch of data to be modified, in the display by inserting data to achieve. This approach is useful when you want to change less than one table record, which is more flexible.

The simpler way to do this is to let the operator redistribute the information and delete the previous data if only a few.

and the Internet by fixing the T-SQL statements to cancel the self-add properties, I did not pass the SQL Server 2005+ Environment test, the corresponding T-SQL code is as follows:

exec sys.sp_configure
@configname = ' allow updates ',--varchar
@configvalue = 1;--int
EXEC sys.sp_conf igure
@configname = ' Show advanced options ',-varchar (
@configvalue = 1;--int
reconfigure with Overrid E;
Go
UPDATE sys.syscolumns
SET colstat = 1
WHERE id = object_id (N ' primarykeyandidentityupdatetestdatatable ', ' U ') and
name = N ' ID ' and
colstat = 1;
UPDATE sys.columns
SET is_identity = 0
WHERE object_id = object_id ( N ' primarykeyandidentityupdatetestdatatable ', ' U ') and
name = N ' ID ' and
is_identity = 1;

The results of the implementation are as follows:

MySQL platform modify self-added column value

MySQL platform modifies the self added column value, there is some trouble. There is an myisam in MySQL, and if its engine is a single column, it can be either a stand-alone primary key column or a composite primary key column, that is, the column must be an associated column of the primary key, or the column must be a stand-alone primary key column if its engine is InnoDB. To directly modify two self-added values to swap changes, certainly not.

The approach I used was to divide two self-added values (such as 1, 2) into the following three steps:
1. The modification of the self-added column value of 1 to 0;
2, and then the self-added column value of 2 modified to 1;
3, and then the self-added column value of 0 modified to 2;

The following two data engine test environments are MySQL 5.6.

Database engine for the premise of InnoDB, the specific MySQL test code is as follows:

drop table if exists identity_datatable;
CREATE TABLE identity_datatable (
ID int not NULL auto_increment, 
name varchar (a) NOT NULL,
primary key (ID) c5/>) Engine=innodb,default Charset=utf8;
INSERT into identity_datatable (ID, name)
values (1, ' 1 '), (2, ' 2 ');
INSERT into identity_datatable (ID, name)
values (3, ' 3 '), (4, ' 4 ');
SELECT * from
identity_datatable;
--Modify Identity_datatable directly--update---
Set id = case When id = 1 then 2 when id = 2 then 1
--where ID in (1, 2);
Update identity_datatable
Set id = 0
where id = 1;
Update identity_datatable
Set id = 1
where id = 2;
Update identity_datatable
Set id = 2
where id = 0;
SELECT * from
identity_datatable;

Data table results before modification, as shown in the following figure:

The modified datasheet results, as shown in the following figure:

Attention:

1, the use of two numbers for the exchange of methods.
2, the introduction of the median value of the best <=0 number.
3, only to provide a solution, you can also use the SQL Server platform to modify the method (1, first remove the self-added properties after the change finally increase the properties, 2, the collation of T-SQL script to reinsert----small amount of data can be, 3, operators manually re-add, but also the case of small amount of data.

Database engine for the premise of MyISAM, the specific MySQL test code is as follows:

drop table if exists Autoincremenet_datatable_myisam;
CREATE TABLE Autoincremenet_datatable_myisam (
tid int not NULL,
ID int. NOT NULL auto_increment,
name varchar () not NULL,
primary key (ID)
) engine = MyISAM, default charset = UTF8;
Insert into Autoincremenet_datatable_myisam (tid, ID, name)
values (1,1, ' a '), (2,2, ' B '), (3,3, ' C '), (4,4, ' d ');
SELECT * from
autoincremenet_datatable_myisam;
Update Autoincremenet_datatable_myisam
Set id = 0;
where id = 1;
SELECT * from
autoincremenet_datatable_myisam;
Update Autoincremenet_datatable_myisam
Set id = 1;
where id = 2;
SELECT * from
autoincremenet_datatable_myisam;
Update Autoincremenet_datatable_myisam
Set id = 2;
where id = 0;
SELECT * from
Autoincremenet_datatable_myisam;

Attention:

1. The changes in the above test can not be done.

2, the question "The first update and its subsequent select do see the modified value, but the subsequent SQL continues to execute, all the errors are returned unchanged before the state", this is not clear, need to continue research.

The Oracle platform is not contacted, and you are not aware that bloggers familiar with the Oracle platform are testing or summarizing their own incremental changes.