April 2, 2012 at 10:30 am
Hello comunity
I see a TSQL script to make UPDATE on large table :
select
'update fl set radicaltipoemp='+convert(varchar,'1')+' from fl where fl.flstamp='''+fl.flstamp+''''
from fl (nolock)
where fl.radicaltipoemp = 0
i think that the purpose is make update row-by-row, like a cursor.
Also, the problem that this TSQL return this result on QA:
update fl set radicaltipoemp=1 from fl where fl.flstamp='7104E83A-7C62-41D4-BB02 '
I suppose that the problem is on closing quotes, or because radicaltipoemp is numeric(1).
someone could give me some help to explain why the TSQL don´t work.
Many thanks
Luis Santos
April 2, 2012 at 10:39 am
luissantos (4/2/2012)
Hello comunityI see a TSQL script to make UPDATE on large table :
select
'update fl set radicaltipoemp='+convert(varchar,'1')+' from fl where fl.flstamp='''+fl.flstamp+''''
from fl (nolock)
where fl.radicaltipoemp = 0
i think that the purpose is make update row-by-row, like a cursor.
Also, the problem that this TSQL return this result on QA:
update fl set radicaltipoemp=1 from fl where fl.flstamp='7104E83A-7C62-41D4-BB02 '
I suppose that the problem is on closing quotes, or because radicaltipoemp is numeric(1).
someone could give me some help to explain why the TSQL don´t work.
Many thanks
Luis Santos
Actually, it is working. All the select is doing is creating the update statements. If you want to run them, you have to copy them to another query window and run them.
Are you trying to automate this process?
April 2, 2012 at 10:44 am
Hello Lynn
i don´t understand your comment:
"you have to copy them to another query window and run them."
Do you refer to copy all the result into another TSQL windows ?
could you give me an example, about i can do that.
best regards
Luis Santos
April 2, 2012 at 10:52 am
luissantos (4/2/2012)
Hello Lynni don´t understand your comment:
"you have to copy them to another query window and run them."
Do you refer to copy all the result into another TSQL windows ?
could you give me an example, about i can do that.
best regards
Luis Santos
your query is dynamically building the update statements. the results you get are strings which if you run in a query window will actually update the table.
if you want to build a script around this to execute the update statements we can help if you can provide table definitions and some sample data to work with. Please see the link in my signature for the way we like to see the DDL and Sample data here. Thanks
EDIT: this may work but there may be a more efficient method im sure we can find it if you post your DDL and sample data
DECLARE @flstamp VARCHAR
DECLARE @CMD VARCHAR(MAX)
DECLARE UpdateCursor CURSOR FOR
SELECT flstamp FROM fl
WHERE fl.radicaltipoemp = 0
ORDER BY flstamp;
OPEN UpdateCursor
FETCH NEXT FROM UpdateCursor
INTO @flstamp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = 'update fl set radicaltipoemp=''1'' from fl where fl.flstamp='''+@flstamp+''''
EXEC (@Cmd)
FETCH NEXT FROM UpdateCursor
INTO @flstamp
END
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:05 am
of course you may be able to get away with this depending on your tables
UPDATE fl SET radicaltipoemp = 1 where radicaltipoemp = 0
does ALMOST the same thing but if making sure the flstamp is in the update it may not be what you need.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 11:09 am
Hello,
I think you need to do this:
DECLARE @Query VARCHAR(MAX)
select
@Query = COALesCE( @Query + CHAR(13) , ' ' ) +
'update fl set radicaltipoemp='+convert(varchar,'1')+' from fl where fl.flstamp='''+fl.flstamp+''''
from fl (nolock)
where fl.radicaltipoemp = 0
EXEC (@Query)
April 2, 2012 at 12:02 pm
It really looks like this should be a single update instead of rbar.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2012 at 12:09 pm
Sean Lange (4/2/2012)
It really looks like this should be a single update instead of rbar.
i agree. of course this could also be inside of a cursor all ready (Similar to one i posted) and the OP is trying to maintain the code.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 2, 2012 at 12:15 pm
capn.hector (4/2/2012)
Sean Lange (4/2/2012)
It really looks like this should be a single update instead of rbar.i agree. of course this could also be inside of a cursor all ready (Similar to one i posted) and the OP is trying to maintain the code.
I suspect it is not already in a cursor. There is nothing in the original post that looks like a cursor. There is neither a @Variable or a "Where current of". Just a stab in the dark of course.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply