January 21, 2014 at 4:28 pm
This is a sample of what I need to do in production. In production the field is xml. I can't have a where clause on the xml attribute I am trying to update. It is not indexed and does not have a default namespace.
Here is a sample of the data I am trying to run on. This data joins and records match
DECLARE @TEMP TABLE (TheStatus int, Thedescription Varchar(20), TheId int, RecordVersion TimeStamp)
DECLARE @RecordVersionTEMP TABLE (TheId int, RecordVersion Bigint)
INSERT INTO @TEMP (TheStatus, Thedescription, TheId)
SELECT 1,'Should be 2',1 UNION ALL
SELECT 2,'Should be 3',2 UNION ALL
SELECT 3,'Should be 1',3 UNION ALL
SELECT 1,'Should be 2',4 UNION ALL
SELECT 2,'Should be 3',5 UNION ALL
SELECT 3,'Should be 1',6
INSERT INTO @RecordVersionTEMP
SELECT TheId, RecordVersion
FROM @TEMP
--**
--Insert Code snips here
--**
SELECT *
FROM @RecordVersionTEMP
SELECT *, CONVERT(Bigint,RecordVersion)
FROM @TEMP
If I use the data from above and run this query where it says "Insert Code Snips Here" I get all 1's which is obviously wrong
UPDATE @TEMP
SET TheStatus =
REPLACE(
REPLACE(
REPLACE(CONVERT(Varchar(20),TheStatus),'1','2'),'2','3'),'3','1')
Then I try to make the query smarter by using the timestamp to Inner Join on. Problem is the replace function updates all records if you do not use a where clause.
UPDATE @TEMP
SET TheStatus =
REPLACE(CONVERT(Varchar(20),TheStatus),'1','2')
FROM @TEMP T
INNER JOIN @RecordVersionTEMP RT ON T.TheId=RT.TheId AND CONVERT(Bigint,T.RecordVersion)=RT.RecordVersion
UPDATE @TEMP
SET TheStatus =
REPLACE(CONVERT(Varchar(20),TheStatus),'2','3')
FROM @TEMP T
INNER JOIN @RecordVersionTEMP RT ON T.TheId=RT.TheId AND CONVERT(Bigint,T.RecordVersion)=RT.RecordVersion
Can I only change the timestamp on the specific records that match without using a where clause? Is there another way to skin this without looping?
The production dataset is quite large meaning the xml is about 30 attributes and there could be 30k in rows to update.
January 21, 2014 at 5:39 pm
I think you just need to alter your first code snippet as follows (forget about timestamp) and use a CASE:
UPDATE @TEMP
SET TheStatus = CASE TheStatus WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 ELSE TheStatus END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 22, 2014 at 1:22 pm
Sorry for the delay in response. Thanks for the help. That is exactly what I needed to do. Since it was XML I had to do a like which has overhead, but it is really the only thing I can think of doing. This is what I ended up with
DECLARE @TEMP TABLE (TheStatus int, Thedescription Varchar(20), TheId int, RecordVersion TimeStamp, TheXml xml)
INSERT INTO @TEMP (TheStatus, Thedescription, TheId, TheXml)
SELECT 1,'Should be 2',1, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>1</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>' UNION ALL
SELECT 2,'Should be 3',2, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>2</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>' UNION ALL
SELECT 3,'Should be 1',3, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>3</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>' UNION ALL
SELECT 1,'Should be 2',4, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>1</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>' UNION ALL
SELECT 2,'Should be 3',5, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>2</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>' UNION ALL
SELECT 3,'Should be 1',6, N'<FlrBuild xmlns="http://tempuri.org/Floor.xsd"><GableTypeFg>1</GableTypeFg><DesignStatusFg>3</DesignStatusFg><TrussEndDetails>0,0</TrussEndDetails></FlrBuild>'
SELECT TheStatus, Thedescription, TheId,
CASE
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>1</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>1</DesignStatusFg>','<DesignStatusFg>2</DesignStatusFg>')
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>2</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>2</DesignStatusFg>','<DesignStatusFg>3</DesignStatusFg>')
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>3</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>3</DesignStatusFg>','<DesignStatusFg>1</DesignStatusFg>')
END ConvertedValues
FROM @TEMP
UPDATE @TEMP
SET TheXml = CONVERT(XML,
CASE
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>1</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>1</DesignStatusFg>','<DesignStatusFg>2</DesignStatusFg>')
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>2</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>2</DesignStatusFg>','<DesignStatusFg>3</DesignStatusFg>')
WHEN CONVERT(NVarchar(Max),TheXml) Like '%'+'<DesignStatusFg>3</DesignStatusFg>'+'%' THEN REPLACE(CONVERT(NVarchar(Max),TheXml),'<DesignStatusFg>3</DesignStatusFg>','<DesignStatusFg>1</DesignStatusFg>')
END)
SELECT *
FROM @TEMP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply