解析SQLServer2005的Top功能
所有人都知道select top 的用法,但很多人还不知道update top 和 delete top 怎么用。以往的做法是set rowcount来指定,其实SQL2005中对于Top语句的增强除了参数化之外还包括对update和delete的支持,但可惜的是还不支持自定义 的order by列。如果要自定义派
所有人都知道select top 的用法,但很多人还不知道update top 和 delete top 怎么用。以往的做法是set rowcount来指定,其实SQL2005中对于Top语句的增强除了参数化之外还包括对update和delete的支持,但可惜的是还不支持自定义 的order by列。如果要自定义派序列可以借助CTE.对于CTE的任何更改都会影响到原始表。
我们看下面的测试代码。
复制代码 代码如下:
set nocount on
use tempdb
go
if (object_id ('tb' ) is not null )
drop table tb
go
create table tb (id int identity (1 , 1 ), name varchar (10 ), tag int default 0 )
insert into tb (name ) select 'a'
insert into tb (name ) select 'b'
insert into tb (name ) select 'c'
insert into tb (name ) select 'd'
insert into tb (name ) select 'e'
/*--更新前两行
id
name
tag
----------- ---------- -----------
1
a
1
2
b
1
3
c
0
4
d
0
5
e
0
*/
update top (2 ) tb set tag = 1
select * from tb
/*--更新后两行
id
name
tag
----------- ---------- -----------
1
a
1
2
b
1
3
c
0
4
d
1
5
e
1
*/
;with t as
(
select top (2 ) * from tb order by id desc
)
update t set tag = 1
select * from tb
/*--删除前两行
id
name
tag
----------- ---------- -----------
3
c
0
4
d
1
5
e
1
*/
delete top (2 ) from tb
select * from tb
/*--删除后两行
id
name
tag
----------- ---------- -----------
3
c
0
*/
;with t as
(
select top (2 ) * from tb order by id desc
)
delete from t
select * from tb
set nocount off
我会在下一篇文章中介绍一个应用,就是很多人关心的如何独占查询(就是一条数据只被一个终端select到)。
如果你感兴趣的话可以自己先思考一下,我给一个提示:
SQLServer2005有一个关键字Output,,它可以将更改和插入的数据输出,我们配合update top就可以模拟出来一个相对高效的独占查询的事物。此功能适合用在并行的任务处理或者消费中。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

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)

Hot Topics

Both vivox100s and x100 mobile phones are representative models in vivo's mobile phone product line. They respectively represent vivo's high-end technology level in different time periods. Therefore, the two mobile phones have certain differences in design, performance and functions. This article will conduct a detailed comparison between these two mobile phones in terms of performance comparison and function analysis to help consumers better choose the mobile phone that suits them. First, let’s look at the performance comparison between vivox100s and x100. vivox100s is equipped with the latest

Detailed explanation of Oracle error 3114: How to solve it quickly, specific code examples are needed. During the development and management of Oracle database, we often encounter various errors, among which error 3114 is a relatively common problem. Error 3114 usually indicates a problem with the database connection, which may be caused by network failure, database service stop, or incorrect connection string settings. This article will explain in detail the cause of error 3114 and how to quickly solve this problem, and attach the specific code

With the rapid development of the Internet, the concept of self-media has become deeply rooted in people's hearts. So, what exactly is self-media? What are its main features and functions? Next, we will explore these issues one by one. 1. What exactly is self-media? We-media, as the name suggests, means you are the media. It refers to an information carrier through which individuals or teams can independently create, edit, publish and disseminate content through the Internet platform. Different from traditional media, such as newspapers, television, radio, etc., self-media is more interactive and personalized, allowing everyone to become a producer and disseminator of information. 2. What are the main features and functions of self-media? 1. Low threshold: The rise of self-media has lowered the threshold for entering the media industry. Cumbersome equipment and professional teams are no longer needed.

PHP Tips: Quickly implement the function of returning to the previous page. In web development, we often encounter the need to implement the function of returning to the previous page. Such operations can improve the user experience and make it easier for users to navigate between web pages. In PHP, we can achieve this function through some simple code. This article will introduce how to quickly implement the function of returning to the previous page and provide specific PHP code examples. In PHP, we can use $_SERVER['HTTP_REFERER'] to get the URL of the previous page

As Xiaohongshu becomes popular among young people, more and more people are beginning to use this platform to share various aspects of their experiences and life insights. How to effectively manage multiple Xiaohongshu accounts has become a key issue. In this article, we will discuss some of the features of Xiaohongshu account management software and explore how to better manage your Xiaohongshu account. As social media grows, many people find themselves needing to manage multiple social accounts. This is also a challenge for Xiaohongshu users. Some Xiaohongshu account management software can help users manage multiple accounts more easily, including automatic content publishing, scheduled publishing, data analysis and other functions. Through these tools, users can manage their accounts more efficiently and increase their account exposure and attention. In addition, Xiaohongshu account management software has

Analysis of new features of Win11: How to skip logging in to a Microsoft account. With the release of Windows 11, many users have found that it brings more convenience and new features. However, some users may not like having their system tied to a Microsoft account and wish to skip this step. This article will introduce some methods to help users skip logging in to a Microsoft account in Windows 11 and achieve a more private and autonomous experience. First, let’s understand why some users are reluctant to log in to their Microsoft account. On the one hand, some users worry that they

PHP is a server-side scripting language widely used in web development. Its main function is to generate dynamic web content. When combined with HTML, it can create rich and colorful web pages. PHP is powerful. It can perform various database operations, file operations, form processing and other tasks, providing powerful interactivity and functionality for websites. In the following articles, we will further explore the role and functions of PHP, with detailed code examples. First, let’s take a look at a common use of PHP: dynamic web page generation: P

"Understanding VSCode: What is this tool used for?" 》As a programmer, whether you are a beginner or an experienced developer, you cannot do without the use of code editing tools. Among many editing tools, Visual Studio Code (VSCode for short) is very popular among developers as an open source, lightweight, and powerful code editor. So, what exactly is VSCode used for? This article will delve into the functions and uses of VSCode and provide specific code examples to help readers
