Need help on ( Keep it existing, Add new ) & ( Remove existing, Add new )

  • I've table & EXISTING data as below,

    001

    Scripts as following,

    CREATE TABLE [dbo].[IncidentProgressAssignPeople](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IncidentProgressId] [int] NOT NULL,
    [ToUserId] [nvarchar](450) NOT NULL,
    [ReplyDate] [datetime] NOT NULL,
    [isInProgress] [bit] NULL,
    [isInProgressDate] [datetime] NULL,
    [isResolved] [bit] NULL,
    [isResolvedDate] [datetime] NULL,
    [dtVersion] [timestamp] NULL,
    CONSTRAINT [PK_IncidentProgressAssignPeople] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[IncidentProgressAssignPeople] ON
    GO
    INSERT [dbo].[IncidentProgressAssignPeople] ([Id], [IncidentProgressId], [ToUserId], [ReplyDate], [isInProgress], [isInProgressDate], [isResolved], [isResolvedDate]) VALUES (4, 145, N'86fb57bd-0cf8-4e55-9b8d-91418720f326', CAST(N'2021-09-13T23:00:30.037' AS DateTime), 0, NULL, 0, NULL)
    GO
    SET IDENTITY_INSERT [dbo].[IncidentProgressAssignPeople] OFF
    GO
    SET ANSI_PADDING ON
    GO
    /****** Object: Index [IncidentProgressAssignPeople_UQ001] Script Date: 13/9/2021 11:31:28 PM ******/ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD CONSTRAINT [IncidentProgressAssignPeople_UQ001] UNIQUE NONCLUSTERED
    (
    [IncidentProgressId] ASC,
    [ToUserId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD CONSTRAINT [DF_IncidentProgressAssignPeople_ReplyDate] DEFAULT (getdate()) FOR [ReplyDate]
    GO
    ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD CONSTRAINT [DF_IncidentProgressAssignPeople_isInProgress] DEFAULT ('false') FOR [isInProgress]
    GO
    ALTER TABLE [dbo].[IncidentProgressAssignPeople] ADD CONSTRAINT [DF_IncidentProgressAssignPeople_isResolved] DEFAULT ('false') FOR [isResolved]
    GO

    There's 2 scenario

    1. Keep it existing, Add new
    2. Remove existing, Add new

    Below script is - Keep it existing, Add New.

    Declare @GetIncidentProgressId int
    Set @GetIncidentProgressId = 145

    Declare @valItem xml

    -- Keep it existing, add new
    Set @valItem = '<data><userId><ToUserId>86fb57bd-0cf8-4e55-9b8d-91418720f326</ToUserId></userId><userId><ToUserId>f1fd0730-28d9-4a1d-bd12-c6af741b04ec</ToUserId></userId></data>'

    select @GetIncidentProgressId,
    a.b.value('ToUserId[1]','nvarchar(450)')
    from
    @valItem.nodes('/data/userId') a(b)
    Except
    Select [IncidentProgressId], [ToUserId] from [dbo].[IncidentProgressAssignPeople]
    Where [IncidentProgressId] = @GetIncidentProgressId

    /*
    (No column name)(No column name)
    145 f1fd0730-28d9-4a1d-bd12-c6af741b04ec
    */

     

    I don't know - How to Remove existing, Add new. So far, I've this. It Add New Data. But not Remove existing

    Declare @GetIncidentProgressId int
    Set @GetIncidentProgressId = 145

    Declare @valItem xml

    -- Remove existing, add new
    Set @valItem = '<data><userId><ToUserId>f1fd0730-28d9-4a1d-bd12-c6af741b04ec</ToUserId></userId><userId><ToUserId>5f7e233f-9f01-43a7-9645-1cb57cacb9c9</ToUserId></userId></data>'

    select @GetIncidentProgressId,
    a.b.value('ToUserId[1]','nvarchar(450)')
    from
    @valItem.nodes('/data/userId') a(b)
    Except
    Select [IncidentProgressId], [ToUserId] from [dbo].[IncidentProgressAssignPeople]
    Where [IncidentProgressId] = @GetIncidentProgressId


    /*
    (No column name)(No column name)
    145 5f7e233f-9f01-43a7-9645-1cb57cacb9c9
    145 f1fd0730-28d9-4a1d-bd12-c6af741b04ec
    */

    Please help. I'm stuck

    • This topic was modified 1 week, 3 days ago by  Adelia.
  • You have data. How about posting CREATE TABLE and INSERT scripts so we can see what you're working with? While you're at it, post the expected result... but for the love of god not as a picture.

    Or did you not want an answer?

  • pietlinden wrote:

    You have data. How about posting CREATE TABLE and INSERT scripts so we can see what you're working with? While you're at it, post the expected result... but for the love of god not as a picture. Or did you not want an answer?

    I did. See above. May be you missing. See, Scripts as following, .....

  • I got the answer. Yeahhh

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply