Wrong Updates when using Like Operator.. Need Urgent solution!

Question

Wrong Updates when using Like Operator.. Need Urgent solution!

Abhijit More

SSCertifiable

Points: 6897
More actions
June 23, 2014 at 4:01 am

#287852

Hello SQLers,
I am facing one issue while using LIKE operator in Update statement. Also want to understand why SQL behaving like this 😎
Below is the scenario:
--Master INFO
Search Operator Set Value
Handling Charge Value 1
Handling Charge - PositioningValue 2
Handling Charge - IntermodalValue 3
Terminal Handling ChargeValue 4
--Transaction table update with Current Update behaviour
Look Search Operator Into Wrong Updates
TERMINAL HANDLING CHARGE - ORIGINValue 1
HANDLING CHARGE - INTERMODAL Value 1
HANDLING CHARGE - POSITIONING Value 1
HANDLING CHARGE Value 1
--WHAT IS EXPECTED
Look Search Operator Into Correct Updates
TERMINAL HANDLING CHARGE - ORIGINValue 4
HANDLING CHARGE - INTERMODAL Value 3
HANDLING CHARGE - POSITIONING Value 2
HANDLING CHARGE Value 1
Appreciate any quick help! Sample Query attached.
Abhijit - http://abhijitmore.wordpress.com

Viewing 8 posts - 1 through 7 (of 7 total)

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Your problem is the text "HANDLING CHARGE" a complete value of its own, but also exists as a part in every other value. You need to alter the JOIN to get the desired update values.

UPDATEa

SETa.Master_Value = COALESCE(b.Set_Value, c.Set_Value)

FROM@tblUPDATE a

LEFT OUTER JOIN@tblMasterTable b ON a.Look_Search_Operator_Into LIKE '%' + b.Search_Operator + '%'

and b.Search_Operator <> 'HANDLING CHARGE'

LEFT OUTER JOIN@tblMasterTable c ON a.Look_Search_Operator_Into LIKE '%' + c.Search_Operator + '%'

and c.Search_Operator = 'HANDLING CHARGE'

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Abhijit More SSCertifiable Points: 6897 More actions · Answer 2

As per my understanding SQL should compare the value one by one if that it the case it should have consider the matching value. Can you please explain the behaviour

Abhijit - http://abhijitmore.wordpress.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

What you're seeing is a cardinality error, and it's why you should be careful when using the UPDATE...FROM syntax. It occurs when the value you're updating in table a has more than one match in table b. The worst thing is that the update goes ahead without any error being reported.

You might consider using the MERGE statement or something like the following syntax:

UPDATE@tblUPDATE

SETMaster_Value = (

SELECT Set_Value

FROM @tblMasterTable b

WHERE Look_Search_Operator_Into LIKE '%' + b.Search_Operator + '%'

)

(but test thoroughly). The latter won't work - it'll produce an error message, but that's better than getting unexpected results, as you are now.

John

thava SSCrazy Points: 2275 More actions · Answer 4

simple because all the transaction table records have these words "HANDLING CHARGE "

with cte as(

SELECTa.* , b.*, Row_number() over(partition by a.Look_Search_Operator_Into order by b.Search_Operator desc ) as X

FROM@tblUPDATE a

JOIN@tblMasterTable b ON

PATINDEX('%'+ b.Search_Operator +'%',a.Look_Search_Operator_Into )>0

)

update cte

set MAster_value= set_value

where x =1

select * from tblUPDATE

Keep it in mind i gave the solution for the only given data

just see this [url= http://sqlfiddle.com/#!6/9efcb/17%5DFiddle%5B/url%5D

hope you got it

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

Abhijit More (6/23/2014)
As per my understanding SQL should compare the value one by one if that it the case it should have consider the matching value. Can you please explain the behaviour

If you want to sequentially compare values, you will need to use the CASE statement. When a condition in a CASE statement is true it will apply the value and exit the CASE statement. No other conditions will be compared.

UPDATEa

SET a.Master_Value = CASE

WHEN a.Look_Search_Operator_Into LIKE '%TERMINAL%' THEN 'Value 4'

WHEN a.Look_Search_Operator_Into LIKE '%INTERMODAL%' THEN 'Value 3'

WHEN a.Look_Search_Operator_Into LIKE '%POSITIONING%' THEN 'Value 2'

WHEN a.Look_Search_Operator_Into LIKE '%HANDLING CHARGE%' THEN 'Value 1'

ELSE a.Master_Value-- leave unchanged

END

FROM@tblUPDATE a

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33506 More actions · Answer 6

Abhijit More (6/23/2014)
As per my understanding SQL should compare the value one by one if that it the case it should have consider the matching value. Can you please explain the behaviour

In my solution using the two LEFT OUTER JOINs I match the values for the first JOIN on everything but the exact value "HANDLING CHARGE" (hence the WHERE value <> 'HANDLING CHARGE' inside the ON statement). The second JOIN is on the exact value (hence the WHERE value = 'HANDLING CHARGE' inside the ON statement). The update statement using a COALESCE will take the first NON-NULL value. It will take the value from the first JOIN ( = b.set_value) or if this doesn't exist it will take the value of the second JOIN ( = c.set_value).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Abhijit More SSCertifiable Points: 6897 More actions · Answer 7

you solution worked Hansi perfectly, thank you for quick response! and thanks everyone for sharing your thoughts

The only point I was concerned about the behavior of SQL Query. I was bit confused about it.

I may ask some silly if required 🙂

Abhijit - http://abhijitmore.wordpress.com