求一条sql server 语句!急急急

[persist_yu20082009]

表有2个字段 worknum , date
新增一条记录时，给出 worknum 和date 值 如： 1234 ， 2010-04-21
如果worknum列有1234这个值，就更新 该记录的 date记录
如果worknum列没有1234这个值，就新增一条记录。
请大家给出语句，万分感激！！！

[王向飞]

if exists （select 1 from tb where worknum = 1234）
begin
update tb 
set date = ' 2010-04-21'
where worknum = 1234
end
else
insert into tb ( worknum, date )values (1234 ， '2010-04-21')

• 打赏
• 举报

回复 赞

[koolxiaoyuer]

CREATE  PROCEDURE pinsert(
@worknum int,
@date datetime
)
as
if exists （select 1 from tb where worknum = @worknum ）
begin
update tb 
set date = @date
where worknum =@worknum 
end
else
insert into tb ( worknum, date )values (@worknum ， @date)
GO

• 打赏
• 举报

回复 赞

[mechellerong]

 create table tb(
 worknum int,
 date datetime
)
insert into tb ( worknum, date )values (1234,'2010-04-21')
insert into tb ( worknum, date )values (1234,'2010-05-21')

select * from tb

结果如下：
----------------------------
Worknum     date
1234	2010-04-21 00:00:00.000
1234	2010-05-21 00:00:00.000

if exists (select 1 from tb where worknum = 1234)
begin
update tb 
set date = ' 2010-04-21'
where worknum = 1234
end
else
insert into tb ( worknum, date )values (1234,'2010-05-21')

结果如下：
--------------------------------------
Worknum     date
1234	2010-04-21 00:00:00.000
1234	2010-04-21 00:00:00.000

• 打赏
• 举报

回复 赞

[无涯大者]

写个存储过程
1.创建表tbA

create table tbA(
 worknum int,
 date datetime
 )

2.创建存储过程

create  PROC pro_insert(@worknum int,@date datetime)
as
if exists (select 1 from tbA where worknum = @worknum)
begin
update tbA set date = @date where worknum =@worknum 
end
else
insert into tbA ( worknum, date )values (@worknum , @date)
GO

3.直接调用存储过程

 Execute pro_insert '1234','2010-6-21'

• 打赏
• 举报

回复 赞

[s_111111]

各位大俠，有一個merge用法 不知道可行不可行。

• 打赏
• 举报

回复 赞

[njlywy]

if exists （select 1 from tb where worknum = 1234）
begin
update tb
set date = ' 2010-04-21'
where worknum = 1234
end
else
insert into tb ( worknum, date )values (1234 ， '2010-04-21')

• 打赏
• 举报

回复 赞