update table based on a condition

Question

I need to update a column in table based on some condition.

here is the table -

DGTID |   SKAcc     |      Linkedaccount        |   DGTStatus 
 1      8002180831     8102651144,8005370302          C
 2      8005370302           8002170111               I
 3      8002012348                                    I

So, for a particular DGT ID, if status = 'C' and if Linkedaccount is present in any other SKAcc, then DGTStatus for that SKAcc should also be C.

Lets say, DGTId = 1, it has DGTStatus as 'C' and it has a linkedaccount = '8005370302' which is the SKAcc for DGTID='2' . So, its DGTStatus should also be 'C'

Now, I believe the main part here is to extract linked account as there can be multiple comma seperated linked account.

This is what I wrote to extract linked account -

select  replace(''''+LinkedAccount+'''',',',''',''')  from IDCOPES..T_CDI_WHT_DGT where ISNULL(LinkedAccount,'')<>''

This gives me '8102651144','8005370302'.

and then, I wrote -

IF EXISTS (SELECT 1 from IDCOPES..T_CDI_WHT_DGT where DGTID=1 and DGTSTATUS='C')
    UPDATE IDCOPES..T_CDI_WHT_DGT
    SET DGTSTATUS='C' 
    Where SKACC in (select  replace(''''+LinkedAccount+'''',',',''',''')  from IDCOPES..T_CDI_WHT_DGT where ISNULL(LinkedAccount,'')<>'' and DGTID=1)

But, this doesnt work. However, when I write

IF EXISTS (SELECT 1 from IDCOPES..T_CDI_WHT_DGT where DGTID=1 and DGTSTATUS='C')
    UPDATE IDCOPES..T_CDI_WHT_DGT
    SET DGTSTATUS='C' 
    Where SKACC in ('8102651144','8005370302')

It works. I can't understand why it won't work when both code are same.

Fix your data model! Don't store lists of numbers as strings! — Gordon Linoff, Jul 11 '19 at 10:50
@GordonLinoff - Thought of doing that but i can't fix structure of table. That can't be changed — geek, Jul 11 '19 at 10:52
Why not, @geek? Numbers and strings have very different sort orders (did you know that `'9'` is greater than `'8999999999999999999'`?) and if you're going to be passing ranges, your queries are going to perform awfully with the required implicit/explicit casting. — Thom A, Jul 11 '19 at 10:56
correcting the structure might seem lots more work then finding a workaround for your current problem. But not fixing it will make that you will need lots and lots more workarounds in the future. Do yourself a favor and fix the structure now — GuidoG, Jul 11 '19 at 10:57
@Larnu Oh. So, when I use that replace statement, it returns me a number? The thing is, datatype of linkedaccount is varchar and i am not authorised to change it. I will have to work a different way out to extract linked account. — geek, Jul 11 '19 at 10:59
@GuidoG i am really sorry but can't change the structure. It is used in other projects as well. I need to figure out another way :( — geek, Jul 11 '19 at 11:01
No, `REPLACE` will return a `varchar`, @geek. My point is, if you had a clause like `WHERE AccountNumber BETWEEN 10000000 AND 20000000 ` you'd either need to cast/convert `AccountNumber` to an `int` (which you can't) or pass the 2 string literals as strings, which would would mean you get wrong results. — Thom A, Jul 11 '19 at 11:02
@Larnu oh. So, if replace returns a varchar, it should work, right? But i am not able to update — geek, Jul 11 '19 at 11:03
If you have to query delimited data, you'll want to look at `STRING_SPLIT` or this question here: https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows. but the **real** answer is fix the data model. — Thom A, Jul 11 '19 at 11:06
@Larnu Oh, i got it. So, if I am able to fix the data model, I should change the datatype from varchar to? — geek, Jul 11 '19 at 11:09
@Larnu I can't use STRING_SPLIT since I am using Sql server 2014 — geek, Jul 11 '19 at 11:11
Yes, if you fix the delimited data and have a database with a proper many to one relationship, then your new table will have a column of the data type `(big)int`, and you can simply use a "proper" `WHERE` like `WHERE LinkedAccount = 1234567890`. — Thom A, Jul 11 '19 at 11:11
Why I linked to an existing answer too @geek; or you could look up `delimitedsplit8k_LEAD`. — Thom A, Jul 11 '19 at 11:12
@Larnu I cant use a where clause like this, WHERE LinkedAccount = 1234567890, because I am calling this statement in another stored procedure. So, i need to extract multiple seperated accounts and I cant directly use a value in where clause — geek, Jul 11 '19 at 11:15
Yes, @geek, that's because of the (bad) design choice, and why you need to use a string splitter; like those I mentioned above (or have the design fixed). — Thom A, Jul 11 '19 at 11:38

B.Muthamizhselvi · Answer 1 · 2019-07-11T14:32:47.853

0

IF EXISTS (SELECT 1 from IDCOPES..T_CDI_WHT_DGT where DGTID=1 and DGTSTATUS='C')
UPDATE IDCOPES..T_CDI_WHT_DGT
SET DGTSTATUS='C' 
 Where SKACC in (
SELECT 
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT  *,CAST('<XMLRoot><RowData>' + REPLACE(LinkedAccount,',','</RowData> 
<RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   IDCOPES..T_CDI_WHT_DGT
where LinkedAccount like '%,%'
)t
 CROSS APPLY x.nodes('/XMLRoot/RowData')m(n))

edited Jul 11 '19 at 14:32

answered Jul 11 '19 at 11:28

B.Muthamizhselvi

642
4
13

I can't use string_split since I am using 2014 version – geek Jul 11 '19 at 11:45
@Geek, i changed my query. please check it. – B.Muthamizhselvi Jul 11 '19 at 14:35

score 0 · Answer 2 · answered Jul 11 '19 at 11:34

You can use the update logic as below-

DECLARE @TempTable TABLE(
    DGTID INT,
    SKAcc VARCHAR(MAX),
    Linkedaccount VARCHAR(MAX),
    DGTStatus VARCHAR(1)
)

INSERT INTO @TempTable (DGTID,SKAcc,Linkedaccount,DGTStatus)
VALUES( 1,'8002180831','8102651144,8005370302','C' ),
( 2,'8005370302','8002170111','I'),
( 3,'8002012348',NULL,'I' )

SELECT * FROM @TempTable

UPDATE A
SET A.DGTStatus = 'C'
FROM @TempTable A
INNER JOIN @TempTable B ON B.Linkedaccount LIKE '%'+CAST(A.SKAcc AS VARCHAR(MAX))+'%'
AND  B.DGTStatus = 'C'

SELECT * FROM @TempTable

update table based on a condition

2 Answers2