如何在SQL Server中更新两个具有引用表的表？

I have 3 tables.

我有3张桌子。

1. Users 4 Cols
UserID - UserName - RealName - Flags

2. UsersGroups 2 Cols
UserID - GroupID

3. Groups 3 Cols
GroupID - GroupName - Flags

What I want to do is select a specific UserName ie USERA and update the Flags column. but I also want to update the Flags column in the Groups table to the same value.

我想要做的是选择一个特定的UserName即USERA并更新Flags列。但我还想将Groups表中的Flags列更新为相同的值。

The only connection I have between the 2 tables is the UsersGroups table.

我在两个表之间唯一的连接是UsersGroups表。

What is the best way to do this?

做这个的最好方式是什么?

5 个解决方案

#1

This should do it:

这应该这样做:

Create Proc spUpdateUsersFlag(@UserName as Varchar(32), @Flags as int)
 AS
Declare @UserID as int

BEGIN Transaction
BEGIN TRY
    SELECT @UserID = UserID 
        From Users 
        Where UserName = @UserName
    UPDATE Users
        SET Flags = @Flags
        WHERE UserID = @UserID
    UPDATE Groups
        SET Flags = @Flags
        FROM Groups G INNER JOIN UserGroups UG ON G.GroupId = UG.GroupID
        WHERE UG.UserID = @UserID
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT
    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()
    -- Rollback the failed transaction
    ROLLBACK;
    -- Raise an error: with the original error information.
    RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH
COMMIT Transaction;

EDIT: Corrected an error in the second query.

编辑:更正了第二个查询中的错误。

#2

An UPDATE statement can only update records of a single table.

UPDATE语句只能更新单个表的记录。

UPDATE Users SET Flags = @Flags WHERE UserID = @UserID

UPDATE Groups SET Flags = @Flags
FROM Groups
INNER JOIN UsersGroups ON UsersGroups.GroupID = Groups.GroupID
WHERE UsersGroups.UserID = @UserID

#3

With respect to select a specific user use the following :

关于选择特定用户,请使用以下内容:

select UserName from Users where UserID = <USERA>

And updatting the flag column in both 2 tables Users and Groups do the following :

并更新两个表中的标志列用户和组执行以下操作:

update Users set Flags = <Your Flag> where UserID = <USERA>
update Groups set Flags = <Your Flag> where GroupID in (select GroupID from UsersGroups where UserID = <USERA>)

Hope that these are useful

希望这些都有用

#4

UPDATE dbo.Users
SET Flags = @var
WHERE UserName = 'UserA'

UPDATE g
SET g.Flags = @var
FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
WHERE u.UserName = 'UserA'

#5

That should really be an atomic unit of work to maintain your data integrity!!! When updating more than one table and they must be kept in sync use BEGIN and COMMIT/ROLLBACK TRAN or if you have Sql Server 2008 use the new TRY CATCH syntax

这应该是维持数据完整性的原子工作单元!更新多个表时,必须保持同步使用BEGIN和COMMIT / ROLLBACK TRAN,或者如果您有Sql Server 2008,请使用新的TRY CATCH语法

BEGIN TRAN
    BEGIN TRY
    UPDATE dbo.Users
    SET Flags = @var
    WHERE UserName = 'UserA'

    UPDATE dbo.Groups
    SET Flags = @var
    FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
        INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
    WHERE u.UserName = 'UserA'
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

END CATCH
IF @@TRANCOUNT > 0
    COMMIT TRAN

As an aside if your data is denormalised for performance then this is your best solution. If that's not the case I recommend you ditch one of the columns. (Waits for typical "It's not my schema I inherited it.. legacy blah... he he :))

另外,如果您的数据因性能而非规范化,那么这是您的最佳解决方案。如果不是这种情况,我建议您抛弃其中一列。 (等待典型的“这不是我的架构我继承了它..遗产等等......他:)))

PS the code inside the transaction block is flagrantly copy/pasted from Chris' answer.

PS交易块中的代码是从Chris的回答中公然复制/粘贴的。

EDIT

There's a lot of comments about ambiguous column names, but there's nothing wrong with the TSQL here. Here is an entire DML and QUERY that I have tested in MSSMS:

关于模糊列名称有很多评论,但这里的TSQL没有任何问题。这是我在MSSMS中测试的整个DML和QUERY:

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 

TABLE_NAME='Users')
BEGIN
    CREATE TABLE Users
    (
        UserID      INT IDENTITY(1,1) PRIMARY KEY,
        UserName    NVARCHAR(32) NOT NULL,
        RealName    NVARCHAR(64) NOT NULL,
        Flags       NVARCHAR(16) NOT NULL
    )
END
GO

IF NOT EXISTS (SELECT ix.name FROM sys.indexes ix WHERE ix.name='IX_Users_UserName')
BEGIN
    CREATE UNIQUE INDEX IX_Users_UserName ON Users(UserName)
END
GO

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Groups')
BEGIN
    CREATE TABLE Groups
    (
        GroupID     INT IDENTITY(1,1) PRIMARY KEY,
        GroupName   NVARCHAR(32) NOT NULL,
        Flags       NVARCHAR(16) NOT NULL
    )
END
GO

IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='UsersGroups')
BEGIN
    CREATE TABLE UsersGroups
    (
        UserID  INT NOT NULL,
        GroupID INT NOT NULL,
        CONSTRAINT PK_UsersGroups PRIMARY KEY CLUSTERED (UserID, GroupID),
        CONSTRAINT FK_UsersGroups_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID),
        CONSTRAINT FK_UsersGroups_GroupID FOREIGN KEY (GroupID) REFERENCES Groups(GroupID),
    )
END
GO

DECLARE @count INT = (SELECT COUNT(*) FROM Users)
IF @count = 0
BEGIN
    INSERT INTO Users(UserName, RealName, Flags)
    SELECT 'USERA', 'User A', 'Flags A'
    UNION ALL
    SELECT 'USERB', 'User B', 'Flags B'
END

SELECT @count = (SELECT COUNT(*) FROM Groups)
IF @count = 0
BEGIN
    INSERT INTO Groups(GroupName, Flags)
    SELECT 'Group A', 'Flags A'
    UNION ALL
    SELECT 'Group B', 'Flags B'
END

SELECT @count = (SELECT COUNT(*) FROM UsersGroups)
IF @count = 0
BEGIN
    INSERT INTO UsersGroups(GroupID, UserID)
    SELECT 1, 1
    UNION ALL
    SELECT 2, 2
END
GO

BEGIN TRAN
    BEGIN TRY
        DECLARE @var NVARCHAR(16)
        SET @var = 'New Flags A'

        UPDATE dbo.Users
        SET Flags = @var
        WHERE UserName = 'UserA'

        UPDATE dbo.Groups
        SET Flags = @var
        FROM dbo.Users u INNER JOIN dbo.UsersGroups ug ON u.UserID = ug.UserID
            INNER JOIN dbo.Groups g ON g.GroupID = ug.GroupID
        WHERE u.UserName = 'UserA'

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRAN

SELECT Flags FROM Users

SELECT Flags FROM Groups