CODEWITHSUNDEEP
sqlsql-servert-sql
Mina Chan
Mina Chan

Reputation: 61

How to add or update data on row but will not duplicate item with the same row that has two same value

enter image description here

I would like to ask for help in my sql command where I would add or update a group. Where as having conditions.

  1. Group can only have 3 different type.
  2. Person can change his type or group name if the type on that group is not yet used
  3. New person is added if he or she is not yet existing on the table but will not be added if he or she selected a type on a group which is already used.
  4. New person is added if he or she create a new group.

What I am having trouble is on how to check if the type on a group is already in used. I have added my query below.

Please if you have questions or corrections I am happy to address. Thank you!

ID     Type    GroupName    Name
-------------------------------
00      1        GROUPA     John
01      2        GROUPA     Jane
02      3        GROUPA     Ted
03      1        GROUPB     Rose
04      2        GROUPB     Eric
05      3        GROUPB     Nico

    IF NOT EXISTS(SELECT * FROM TABLEA as ATG WHERE @ID = ATG.ID)
            BEGIN
                INSERT INTO TABLEA(ID,TYPE,GroupName,Name)
                VALUES (@ID,@TYPE,@GroupName,@Name)
            END
    ELSE
            BEGIN
                UPDATE TABLEA SET [ID] = @ID,[TYPE] = @TYPE ,[GroupName]=@GroupName, [Name]= @Name
                WHERE ID = @ID
            END

Upvotes: 0

Views: 258

Answers (1)

Robert Sheahan
Robert Sheahan

Reputation: 2100

You have 2 distinct tests, you need to allow for 4 possible outcomes. I marked the "didn't do" outputs with a PRINT statement, though you'll have to "set output to text" to see it.

--Test with each of the casews below. Make sure to "Set output to Text" to see the PRINT statements
  DECLARE @ID int = 1; DECLARE @GType INT = 3; DECLARE @GName varchar(50) = 'GROUPB'; DECLARE @UName varchar(50) = 'Jane'; --Jane tries to move to taken group (3-B is taken by Nico)
--DECLARE @ID int = 1; DECLARE @GType INT = 3; DECLARE @GName varchar(50) = 'GROUPC'; DECLARE @UName varchar(50) = 'Jane'; --Jane tries to move to free group (3-C)
--DECLARE @ID int = 6; DECLARE @GType INT = 3; DECLARE @GName varchar(50) = 'GROUPB'; DECLARE @UName varchar(50) = 'Alice'; --New user tries to enter taken group (3-B is taken by Nico)
--DECLARE @ID int = 6; DECLARE @GType INT = 3; DECLARE @GName varchar(50) = 'GROUPC'; DECLARE @UName varchar(50) = 'Alice'; --New user enters free group (3-C)

DECLARE @TABLEA TABLE (ID Int, GType int, GName nvarchar(50), UName nvarchar(50))
INSERT INTO @TABLEA(ID, GType, GName, UName)
VALUES (0, 1, 'GROUPA', 'John'), (1, 2, 'GROUPA', 'Jane'), (2, 3, 'GROUPA', 'Ted')
    , (3, 1, 'GROUPB', 'Rose'), (4, 2, 'GROUPB', 'Eric'), (5, 3, 'GROUPB', 'Nico')

IF EXISTS (SELECT * FROM @TABLEA WHERE ID = @ID) BEGIN --Existing user
    --And wants to switch to a free group
    IF NOT EXISTS (SELECT * FROM @TABLEA WHERE GType = @GType AND GName = @GName )
        --then update them
        UPDATE @TABLEA SET GType = @GType, GName = @GName, UName = @UName
        WHERE ID = @ID
    ELSE
        PRINT('Existing user cannot change group/type, that group/type combination is taken')
END ELSE BEGIN--new user, is the Group/Type combo available?
    IF NOT EXISTS (SELECT * FROM @TABLEA WHERE GType = @GType AND GName = @GName )
        --then INSERT them
        INSERT INTO @TABLEA (ID, GType, GName, UName)
        VALUES (@ID, @GType, @GName, @UName)
    ELSE
        PRINT('New user cannot be assigned, that group/type combination is taken')
END

SELECT * FROM @TABLEA

Upvotes: 1

Related Questions