Update column based on condition

Question

1.00/5 (2 votes)

See more:

Hi,

i have a Master table like below

SQL

create table #MasterTable (ID float, CValue varchar(10), LimitFrom float, LimitTo float)
insert into #MasterTable values (84450, 'ABC', 1, 200)
insert into #MasterTable values (84451, 'DFE', 12, 20)
insert into #MasterTable values (82040, 'XYZ', 2.5, 6.5)
insert into #MasterTable values (82565, 'AS', 42.1, 62.5)
insert into #MasterTable values (82565, 'RBS', 1, 50)
select * from #MasterTable
drop table #MasterTable

and TxnTable like below

SQL

create table #TxnTable (ID float, FormatString NVARCHAR(100), GivenDate Date, Comment NVARCHAR(10))

insert into #TxnTable values (84450, ';T=Result CValue="ABC" Value="181" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (84451, 'NULL', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (82040, ';T=Result CValue="XYZ" Value="4.6" U="02/03/2021";', '2021-03-02 00:00:00.000', '');
insert into #TxnTable values (83036, ';T=Result CValue="A1C" Value="7.2" U="06/03/2021";', '2021-03-02 00:00:00.000', '');
insert into #TxnTable values (82565, ';T=Result CValue="CR" Value="3.2" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (1234, ';T=Result CValue="CR" Value="3.2" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (82947, ';T=Result CValue="RBS" Value="98" U="05/03/2021";', '2021-03-05 00:00:00.000', '');

select * from #TxnTable
drop table #TxnTable

What I have tried:

i need to update the TxnTable Comment as 'Matched' or 'MisMatched' based on below condition

In TxnTable FormatString 'CValue' should match with master table 'Value'

In TxnTable FormatString 'Value' should be between Master Table 'LimitFrom' and 'LimitTo' Column

In TxnTable FormatString 'U' Date should match with TxnTable 'GivenDate' Column

if the above condition met update comment as 'Matched' else update 'MisMatched'
what is the best way to write query this situation?

Posted 21-Apr-21 22:01pm

kasimmohamed

Updated 22-Apr-21 0:12am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-04-22T00:12:00

The best way would be to re-think your TxnTable and separate out the elements of FormatString into separate columns. If possible, don't do it in the first place, but if that is not possible you should create another temporary table along the lines of

SQL

create table #TxnTable2 (ID float, T nvarchar(10), CValue nvarchar(10), [Value] float, U date, GivenDate Date, Comment NVARCHAR(10))

If you have SQL Server 2016 or better then you can use the STRING_SPLIT[^] function along with the REPLACE[^] function (to remove the ;T=, CValue= and the quotation marks)

If you don't do that then your joins are going to end up looking like this ...

SQL

update a 
set Comment = 'Matched'
from #TxnTable a
INNER JOIN #MasterTable b on b.CValue = LEFT(SUBSTRING(FormatString, CHARINDEX('CValue="', FormatString) + 8, LEN(formatstring)), CHARINDEX('"',SUBSTRING(FormatString, CHARINDEX('CValue="', FormatString) + 8, LEN(formatstring))) - 1)
where CHARINDEX('CValue="', FormatString) > 0

I've only done one of them because it upsets me to do more.

The update becomes trivial after that. You can either set everything to MisMatched and then correct the matching entries e.g.

SQL

update #TxnTable2 set Comment = 'MisMatched'
update a 
set Comment = 'Matched'
from #TxnTable2 a
INNER JOIN #MasterTable b ON a.CValue =b.CValue
where a.[Value] between b.LimitFrom and b.LimitTo
and a.U = a.GivenDate

Or you can use a CASE statement e.g.

update a 
set Comment = CASE WHEN a.[Value] between b.LimitFrom and b.LimitTo AND a.U = a.GivenDate
				THEN 'Matched'
			  ELSE 'MisMatched' END
from #TxnTable2 a
INNER JOIN #MasterTable b ON a.CValue =b.CValue

It's largely down to personal preferences, I personally think the first version is easier to read.

Couple of other points,
- use NULL instead of 'NULL' when splitting out your values
- be consistent in your date formats and use ISO 8601 where possible. E.g. U="05/03/2021" should be either U="2021-03-05" or U="2021-05-03"