有个SQL语句写不出来,请高手帮忙看看
来源:百度知道 编辑:UC知道 时间:2024/06/17 03:18:45
下面是两张表,我想问下,根据temp2表的id字段将数据修改到temp1
create table temp1(
id varchar(20),
name varchar(20),
constraint pk_t1 primary key(id)
)
insert into temp1 values('N001','tom')
go
create table temp2(
id varchar(20),
name varchar(20),
constraint pk_t2 primary key(id)
)
insert into temp2 values('N001','susan')
别人要求是用一句话。。。我就不明白一句话到底是什么个意思
create table temp1(
id varchar(20),
name varchar(20),
constraint pk_t1 primary key(id)
)
insert into temp1 values('N001','tom')
go
create table temp2(
id varchar(20),
name varchar(20),
constraint pk_t2 primary key(id)
)
insert into temp2 values('N001','susan')
别人要求是用一句话。。。我就不明白一句话到底是什么个意思
就是一个句子的意思
update temp1 set name = b.name,constraint = b.constraint from temp1 a,temp2 b where a.id = b.id
BEGIN TRANSACTION
BEGIN TRY
SELECT
IDENTITY(INT,1,1) AS ROW_NUMBER,
CONVERT(INT,ID) AS ID,
NAME
INTO #TEMP
FROM TEMP2
DECLARE @ROW INT,@MAXROW INT,@ID INT,@NAME VARCHAR(MAX)
SET @ROW = 1
SELECT @MAXROW=COUNT(1) FROM #TEMP
WHILE @ROW<=@MAXROW
BEGIN
SELECT @ID=ID,@NAME=NAME FROM #TEMP
UPDATE TEMP1 SET NAME=@NAME WHERE ID=@ID
SET @ROW=@ROW + 1
END
DROP TABLE #TEMP
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH