SQL Server从两个表插入更新

i have 2 tables Winner and player , containing standard information(last name,first name,ID ...) plus two boolean columns Validity and WinnerState(default to 0).

我有两个表赢家和玩家，包含标准信息(姓，名，ID…)和两个布尔列有效性和WinnerState(默认为0)。

what i want to do is create a SQL Server procedure that will randomly select one record from the Players Table and then insert it into Winner Table and update in the same time the WinnerState column of the record selected in the player Table.

我要做的是创建一个SQL Server过程，该过程将从player表中随机选择一条记录，然后将它插入到胜利者表中，同时更新player表中选择的记录的WinnerState列。

the Winner table contain a foreign key to the Player, to keep trace of this player selected.

获胜的表格包含玩家的外键，以保持该玩家的追踪被选中。

i've already tried one procedure but it updates this column for all the player Table:

我已经尝试了一个过程，但是它更新了所有玩家表的这个列:

create procedure selectUpdate
    As
    insert into Winner (Fname,Lname,Adress,City,Tel,Player_ID) from
    select TOP 1 (Fname,Lname,Adress,City,Tel,ID) from Player order by NEWID()
    Update Player
    SET WinnerState=1

HOw can i make it update only the record selected randomly? Thank you!

如何使它只更新随机选择的记录?谢谢你！

2 个解决方案

#1

Assuming that ID is an INT and the primary key of the Player table:

假设ID是一个INT型和玩家表的主键:

DECLARE @id INT

SELECT TOP 1 @id = ID FROM Player ORDER BY NEWID()

insert into Winner (Fname,Lname,Adress,City,Tel,Player_ID) from
select Fname,Lname,Adress,City,Tel,ID from Player WHERE ID = @id

Update Player
SET WinnerState=1 WHERE ID = @id

#2

You can do all in one statement:

你可以在一句话中做到:

create procedure selectUpdate
as
begin
    set nocount on

    ;with p as (
       select top 1 Fname,Lname,Adress,City,Tel,ID,WinnerState
       from Player
       where WinnerState = 0 -- this is necessary I assume
       order by checksum(NEWID())
    )
    update p
    set WinnerState = 1
    output
        inserted.Fname,
        inserted.Lname,
        inserted.Adress,
        inserted.City,
        inserted.Tel,
        inserted.ID into Winner (Fname,Lname,Adress,City,Tel,Player_ID)
end

#1