CODEWITHSUNDEEP
sqlsql-server
user6098208
user6098208

Reputation: 1

Update column on basis of comma separated field comparison

I have a table Client with columns ClientID, Name

ClientID|Name
1       |A
2       |B
3       |C
4       |D
5       |E

A Users table with columns : UserID ,AllowedClients, DeniedClients

USERID |AllowedClients|DeniedClients
U1     |1,2,5         |NULL
U2     |2,1           |NULL
U3     |1,4,5         |NULL
U4     |5,1,3         |NULL
U5     |2,3           |NULL

Currently all DeniedClients are set to NULL.

I need to update The Users table, something like : 

Update users set DeniedClients= (All existing ClientIDs in Client table) –` (users.AllowedClients)

OutPut:

USERID |AllowedClients|DeniedClients
U1     |1,2,5         |3,4
U2     |2,1           |3,4,5
U3     |1,4,5         |2,3
U4     |5,1,3         |2,4
U5     |2,3           |1,4,5

How to write this query without using cursor.

Upvotes: 0

Views: 224

Answers (2)

Veljko89
Veljko89

Reputation: 1953

As other says, structure is bad ... but still there is a way to do it, this is how I would do it in T-SQL ...

EDIT: Fixed part of 1 matching with 11 ... more testing needed, but working on look

if object_id('tempdb..#Client') is not null drop table #Client
create table #Client (ClientID int, Name nvarchar(5))

insert into #Client(ClientID, Name)
values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E'),
(11, 'F')

if object_id('tempdb..#Users') is not null drop table #Users
create table #Users (UserID nvarchar(5),AllowedClients nvarchar(50),
                     DeniedClients nvarchar(50) null)

insert into #Users (UserID, AllowedClients)
values
('U1', '1, 2, 5'),
('U2', '2, 1'),
('U3', '1, 4, 5'),
('U4', '5, 1, 3'),
('U5', '2, 3'),
('U6', '11, 4, 5')

    update usr1
set DeniedClients = (STUFF((SELECT ', ' + cast(clt.ClientID as nvarchar)
                    from #Users usr
                join #Client clt 
                on  (usr.AllowedClients  not like 
                    '%' + cast(clt.ClientID as nvarchar) + '%' and 
                    SUBSTRING(usr.AllowedClients, 
                    PATINDEX('%' + cast(clt.ClientID as nvarchar) + '%' , 
                    usr.AllowedClients) + 1, 1) <> cast(clt.ClientID as nvarchar))
                or
                    (usr.AllowedClients  like 
                    '%' + cast(clt.ClientID as nvarchar) + '%' and 
                    SUBSTRING(usr.AllowedClients, 
                    PATINDEX('%' + cast(clt.ClientID as nvarchar) + '%' ,
                    usr.AllowedClients) + 1, 1) = cast(clt.ClientID as nvarchar))
                    where usr.UserID = usr1.UserID
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,''))
from #Users usr1

select * from #Users

Upvotes: 1

gotqn
gotqn

Reputation: 43646

The output below is produced by the following code:

enter image description here

DECLARE @Client TABLE
(
    [ClientID] TINYINT
   ,[Name] VARCHAR(12)
)

INSERT INTO @Client ([ClientID], [Name])
VALUES (1, 'A')
      ,(2, 'B')
      ,(3, 'C')
      ,(4, 'D')
      ,(5, 'E');

DECLARE @Users TABLE
(
    [UserID] CHAR(2)
   ,[AllowedClients] VARCHAR(32)
   ,[DeniedClients] VARCHAR(32)
);

INSERT INTO @Users ([UserID], [AllowedClients], [DeniedClients])
VALUES ('U1', '1,2,5', NULL)
      ,('U2', '2,1', NULL)
      ,('U3', '1,4,5', NULL)
      ,('U4', '5,1,3', NULL)
      ,('U5', '2,3', NULL);

WITH DataSource ([UserID], [ClientID]) AS
(
    -- getting all combinations
    SELECT U.[UserID]
          ,C.[ClientID]
    FROM @Users U
    CROSS APPLY @Client C
    -- excluding current values
    EXCEPT
    -- getting current values
    SELECT [UserID]
          ,T.c.value('(./text())[1]', 'nvarchar(4000)') [ClientID]
    FROM @Users U
    CROSS APPLY 
    ( 
        SELECT x = CONVERT(XML, '<i>'  + REPLACE([AllowedClients], ',', '</i><i>') + '</i>').query('.')
    ) DS
    CROSS APPLY x.nodes('i') AS T(c)
) 
SELECT DISTINCT DS1.[UserID]
      ,DS.[CSV]
FROM DataSource DS1
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT CONCAT(',', DS2.[ClientID])
            FROM DataSource DS2
            WHERE DS1.[UserID] = DS2.[UserID]
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
    ,1
    ,1
    ,''
    )
) DS(CSV)

You can now perform your update.

If this is a real project and there are many records it will be faster to normalized your data. If you are stuck to the CSV format because of other reasons, you should write some CLR functions for splitting/concatenating values.

Upvotes: 0

Related Questions