Computer Tutorials
Computer Knowledge
In SQL, how to batch update a certain part of a specific data field in a table
In SQL, how to batch update a certain part of a specific data field in a table
Jan 16, 2024 pm 10:09 PM
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 analyzerselect 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!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Google Translate Picture | Translate Text in Images - MiniTool
Jul 12, 2025 am 12:57 AM
This Google translate picture guide shows you how to translate text from an image. If you are looking for more computer tips and solutions, you can visit php.cn Software official website where you can also find some useful computer tools like php.cn
How to Install Device Drivers Manually on Windows 11/10? - MiniTool
Jul 06, 2025 am 12:15 AM
If your Windows 11/10 computer doesn’t automatically the latest versions of device drivers, you will need to manually install them. In this post, php.cn Software will show you 3 different methods to manually install drivers on your device.
How to Amplify/Boost/Increase Microphone Volume Windows 11? - MiniTool
Jul 06, 2025 am 12:27 AM
This post delivered by php.cn official web page introduces three methods to improve microphone volume and boost its performance, in Control Panel, via Settings, and by Device Manager. Read the below content to view details.
what is an operating system
Jul 11, 2025 am 03:16 AM
The operating system is the basic software for managing hardware resources, running programs, and providing user interaction interfaces. It coordinates the relationship between hardware and software and is responsible for memory allocation, device scheduling, file management and multitasking. Common systems include Windows (suitable for office and gaming), macOS (Apple devices, suitable for creative work), Linux (open source, suitable for developers), and Android/iOS (mobile device system). The choice of ordinary users depends on the usage scenario, such as software compatibility, security and customization requirements. How to view system information: Use winver command for Windows, click on the machine for macOS, use terminal commands for Linux, and find the phone in settings. The operating system is the underlying tool for daily use,
Best Ways to Fix Windows 11/10 Control Panel Not Opening!
Jul 08, 2025 am 12:01 AM
Have you ever wanted to adjust computer settings to fix some issues but suffered from Control Panel not opening? There is nothing more frustrating than this app not turning on, stopping you from viewing and changing system settings. In this post, mul
How to Open and Run dxdiag.exe on Windows 10/11
Jul 06, 2025 am 12:23 AM
This post includes answers for what is dxdiag, how to run dxdiag in Windows 10/11, DirectX Diagnostic Tool’s main functions, and how to update dxdiag.exe driver. php.cn Software also provides many other computer tips and solutions for users. You can
What Is Dell Digital Locker? How to Log in and Use It on Dell PC? - MiniTool
Jul 07, 2025 am 12:28 AM
What is Dell Digital Locker? How to log into Dell Digital Locker? This post from php.cn provides answers. Besides, you can know how to use your Dell Digital Locker to find software products included with your Dell computer.
How to Open Windows 11 Computer Management Console in 7 Ways? - MiniTool
Jul 09, 2025 am 12:18 AM
This essay summarized by php.cn Software mainly teaches you how to open Windows 11 Computer Management with Windows Search, Quick Link menu, Run dialog, command prompt, PowerShell, File Explorer, Control Panel, as well as a desktop shortcut.
