java否则_检查是否存在行，否则插入

回答(11)

2 years ago

我在写我的解决方案 . 我的方法不能代表'if'或'merge' . 我的方法很简单 .

INSERT INTO TableName (col1,col2)

SELECT @par1, @par2

WHERE NOT EXISTS (SELECT col1,col2 FROM TableName

WHERE col1=@par1 AND col2=@par2)

例如：

INSERT INTO Members (username)

SELECT 'Cem'

WHERE NOT EXISTS (SELECT username FROM Members

WHERE username='Cem')

Explanation:

(1)SELECT col1，col2 FROM TableName WHERE col1 = @ par1 AND col2 = @ par2它从TableName中选择搜索值

(2)SELECT @ par1，@ par2 WHERE NOT EXISTS如果不存在(1)子查询则需要

(3)插入TableName(2)步长值

2 years ago

我假设每个航班都有一排？如果是这样：

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)

BEGIN

--UPDATE HERE

END

ELSE

BEGIN

-- INSERT HERE

END

我假设我所说的，因为你做事的方式可以超额预订航班，因为当最多10张票并且你预订20时它会插入一个新行 .

2 years ago

您可以使用Merge功能来实现 . 否则你可以这样做：

declare @rowCount int

select @rowCount=@@RowCount

if @rowCount=0

begin

--insert....

2 years ago

解决此问题的最佳方法是首先使数据库列为UNIQUE

ALTER TABLE table_name ADD UNIQUE KEY

THEN INSERT IGNORE INTO table_name ，如果该值导致表中存在重复键/，则不会插入该值 .

2 years ago

我终于能够使用以下模型插入一行，条件是它尚不存在：

INSERT INTO table ( column1, column2, column3 )

(

SELECT $column1, $column2, $column3

WHERE NOT EXISTS (

SELECT 1

FROM table

WHERE column1 = $column1

AND column2 = $column2

AND column3 = $column3

)

)

我找到了：

2 years ago

在测试行的存在时，传递updlock，rowlock，holdlock提示 .

begin tran /* default read committed isolation level is fine */

if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)

/* insert */

else

/* update */

commit /* locks are released here */

updlock提示会强制查询对该行进行更新锁定(如果该行已存在)，从而阻止其他事务在您提交或回滚之前对其进行修改 .

holdlock提示会强制查询执行范围锁定，从而阻止其他事务添加符合筛选条件的行，直到您提交或回滚为止 .

rowlock提示强制将粒度锁定到行级而不是默认页面级别，因此您的事务不会阻止尝试更新同一页面中不相关行的其他事务(但要注意减少争用和增加之间的权衡)锁定开销 - 您应该避免在单个事务中占用大量行级锁定 .

请注意，锁被视为执行它们的语句 - 调用begin tran不会让你免除另一个事务在你到达之前对某些事情进行锁定 . 您应该尝试通过尽快提交事务(获取较晚，尽早发布)来使SQL在最短的时间内保持锁定 .

请注意，如果您的PK是bigint，则行级锁定可能效率较低，因为SQL Server上的内部哈希值对于64位值是简并的(不同的键值可能会散列到相同的锁定ID) .

2 years ago

看一下MERGE命令 . 你可以在一个陈述中做 UPDATE ， INSERT 和 DELETE .

这是一个使用 MERGE 的工作实现

在进行更新之前检查航班是否已满，否则进行插入 .

if exists(select 1 from INFORMATION_SCHEMA.TABLES T

where T.TABLE_NAME = 'Bookings')

begin

drop table Bookings

end

GO

create table Bookings(

FlightID int identity(1, 1) primary key,

TicketsMax int not null,

TicketsBooked int not null

)

GO

insert Bookings(TicketsMax, TicketsBooked) select 1, 0

insert Bookings(TicketsMax, TicketsBooked) select 2, 2

insert Bookings(TicketsMax, TicketsBooked) select 3, 1

GO

select * from Bookings

然后 ...

declare @FlightID int = 1

declare @TicketsToBook int = 2

--; This should add a new record

merge Bookings as T

using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S

on T.FlightID = S.FlightID

and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)

when matched then

update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook

when not matched then

insert (TicketsMax, TicketsBooked)

values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

2 years ago

这是我最近不得不做的事情：

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]

(

@CustomerID AS INT,

@UserName AS VARCHAR(25),

@Password AS BINARY(16)

)

AS

BEGIN

IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0

BEGIN

INSERT INTO [tblOnline_CustomerAccount] (

[CustomerID],

[UserName],

[Password],

[LastLogin]

) VALUES (

/* CustomerID - int */ @CustomerID,

/* UserName - varchar(25) */ @UserName,

/* Password - binary(16) */ @Password,

/* LastLogin - datetime */ NULL )

END

ELSE

BEGIN

UPDATE [tblOnline_CustomerAccount]

SET UserName = @UserName,

Password = @Password

WHERE CustomerID = @CustomerID

END

END

2 years ago

完整的解决方案如下(包括游标结构) . 非常感谢Cassius Porcus上面发布的 begin trans ... commit 代码 .

declare @mystat6 bigint

declare @mystat6p varchar(50)

declare @mystat6b bigint

DECLARE mycur1 CURSOR for

select result1,picture,bittot from all_Tempnogos2results11

OPEN mycur1

FETCH NEXT FROM mycur1 INTO @mystat6, @mystat6p , @mystat6b

WHILE @@Fetch_Status = 0

BEGIN

begin tran /* default read committed isolation level is fine */

if not exists (select * from all_Tempnogos2results11_uniq with (updlock, rowlock, holdlock)

where all_Tempnogos2results11_uniq.result1 = @mystat6

and all_Tempnogos2results11_uniq.bittot = @mystat6b )

insert all_Tempnogos2results11_uniq values (@mystat6 , @mystat6p , @mystat6b)

--else

-- /* update */

commit /* locks are released here */

FETCH NEXT FROM mycur1 INTO @mystat6 , @mystat6p , @mystat6b

END

CLOSE mycur1

DEALLOCATE mycur1

go

2 years ago

INSERT INTO [DatabaseName1].dbo.[TableName1] SELECT * FROM [DatabaseName2].dbo.[TableName2]

WHERE [YourPK] not in (select [YourPK] from [DatabaseName1].dbo.[TableName1])

2 years ago

INSERT INTO table ( column1, column2, column3 )

SELECT $column1, $column2, $column3

EXCEPT SELECT column1, column2, column3

FROM table