How do I update two tables that have a reference table in SQL Server?

Question

I have 3 tables.

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.

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

What is the best way to do this?

Answer 1

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

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

Answer 2

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'

Answer 3

Your schema is a big troublesome.

Without getting into that detail, I will show you how to update

UPDATE Groups SET Flags = <value>
FROM Users, UserGroups
WHERE Users.UserName = 'USERA' AND 
      Users.UserID = UserGroups.UserID AND 
      Groups.GroupID = UserGroups.GroupID

However it looks like Flags is a per-usergroup value, which is denormalised onto the User table.

I would remove it from the Users table and only update it on the Groups table.

If each user really has their own Flags, then it probably should not be on the Groups table in that case.

Answer 4

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

Answer 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 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.

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:

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

Answer 6

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.