In SQL, how to batch update a certain part of a specific data field in a table

In SQL. How to batch modify part of a certain item of data in a table

The best way is to modify in batches, that is, modify 5,000 items each time (do not modify more than 10,000 items at a time, otherwise it will affect performance).

Although we can use the merge command in 11g, it is best to make partial modifications and impact assessments first, because such operations are risky in a production environment.

If a misoperation occurs, it is best to ask the DBA to restore it. Although you will be blamed, it is still worse than making mistakes after mistakes and losing the chance of recovery.

If you are really confident about these modifications and only consider performance, you can use the following method (pk_col is the primary key of the table):

merge into xxx aa

using (select pk_col from xxx) bb

on (aa.pk_col=bb.pk_col)

when matched then

update set aa.datatype=66 where aa.datatype is null;

How to batch modify data table contents in SQL

Remove blanks first and then replace. If the blank space is a space, it will be easy to handle. Use the functions rtrim and ltrim to remove the space. If it is not a space,

You paste the data in sql into notepad, and then enter

in the sql query analyzer

select ascii ('that blank character') to get the ascii code of this blank, then

update table name set field name = replace (field name, char (just got the ascii code), '')

Just use your statement after removing all the blanks.

There is another way, which is to write all the blank spaces once. For example, a carriage return is char(13), a line break is char(10), etc. You can update the table name set field name = replace (field name ,char(10),'') ,

All possibilities have been replaced, only those are blank, and there are not many cases

How to batch modify part of a column of data using SQL statements?

update yourTable set RECTIME = dateadd(yy,2,RECTIME)

Explanation: select dateadd(yy,3,getdate())

DATEADD ( datepart , number, date )

parameter

datepart

is a parameter that specifies to which part of the date the new value should be returned. The following table lists the date parts and abbreviations recognized by Microsoft® SQL Server™.

Date part abbreviation

Year yy, yyyy

quarter qq, q

Month mm, m

dayofyear dy, y

Day dd, d

Week wk, ww

Hour hh

minute mi, n

second ss, s

millisecond ms

The above is the detailed content of In SQL, how to batch update a certain part of a specific data field in a table. For more information, please follow other related articles on the PHP Chinese website!