August 25, 2021 at 7:06 pm
I need to update/replace category data in the metadata column. When I run my query the category isn't updating with the replacement data. Below is the query I've written, What am I doing wrong? I need to replace Category= with Category=Vesting Deed
BEGIN TRANSACTION
SELECT F.*
, WalMart_ChainOfTitle.*
FROM WalMart_ChainOfTitle
RIGHT OUTER JOIN dbo.tbl_File AS F ON F.ProcessCollectionId = WalMart_ChainOfTitle.ProcessCollectionId
WHERE F.Deleted = 0
AND WalMart_ChainOfTitle.ProjectId = 1001
AND WalMart_ChainOfTitle.CurrentVesting = 1
AND F.MetaData NOT LIKE '%Category=Vesting Deed%'
BEGIN TRANSACTION
UPDATE dbo.tbl_File
SET dbo.tbl_File.MetaData = ((REPLACE(cast(MetaData AS varchar(max)), 'Category=', 'Category=Vesting Deed')
FROM WalMart_ChainOfTitle
RIGHT OUTER JOIN dbo.tbl_File AS F ON F.ProcessCollectionId = WalMart_ChainOfTitle.ProcessCollectionId
WHERE F.Deleted = 0
AND WalMart_ChainOfTitle.ProjectId = 1001
AND WalMart_ChainOfTitle.CurrentVesting = 1
AND F.MetaData NOT LIKE '%Category=Vesting Deed%'
August 25, 2021 at 9:33 pm
What is the datatype/size of tbl_File.MetaData? (wondering why are you casting the type in the replace statement)?
Do you have MetaData column values that contain "Category=" that do not have a value after the equal sign?
Do you have MetaData column values that contain "Category=" with a value after the equal sign? If so, you will effectively be concatenating the values. I doubt if that is your intention.
e.g.,
DECLARE @Metadata VARCHAR(MAX) = 'ID=1,Category=Furniture'
SELECT @Metadata, REPLACE(@Metadata,'Category=','Category=Vesting Deed')
WHERE @Metadata NOT LIKE '%Category=Vesting Deed%'
results in "ID=1,Category=Vesting DeedFurniture"
If Category values may be populated, and you don't want that concatenated with "Vesting Deed", you should probably provide some sample data.
(If they are in a guaranteed order, and delimiting is consistent, it may be easier to isolate the Category key/value pair and avoid the concatenation bug)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy