October 25, 2011 at 10:12 am
I'm trying to do many updates to the same column in a table. It is a text column that needs some items replaced for each record in the same column. I don't want to have to create 10 separate statements to get this done if I don't have to. The code I'm using is below:
update WebTable
set
HTML = Replace(cast(HTML as nvarchar(max)), 'Ralph Brown', 'John J. Gaskill'),
HTML = Replace(cast(HTML as nvarchar(max)), 'John Gaskill', 'John J. Gaskill'),
HTML = Replace(cast(HTML as nvarchar(max)), '972-934-4212', '260-245-1181'),
HTML = Replace(cast(HTML as nvarchar(max)), 'jgaskill@marketscout.com', 'jgaskill@marketscoutwholesale.com'),
HTML = Replace(cast(HTML as nvarchar(max)), '12700 Park Central Drive, Suite 300', '<!--12700 Park Central Drive, Suite 300-->'),
HTML = Replace(cast(HTML as nvarchar(max)), 'Dallas, TX 75251', '<!--Dallas, TX 75251-->'),
HTML = Replace(cast(HTML as nvarchar(max)), 'Fax: 972-934-4299', '<!--Fax: 972-934-4299-->')
where
((CyberAgent_Id = 688 and Category_Id = 938) or --Contractors
(CyberAgent_Id = 620 and Category_Id = 200) or --General Liability
(CyberAgent_Id = 663 and Category_Id = 909) or --Sports and Entertainment
(CyberAgent_Id = 638 and Category_Id = 878) or --Healthcare
(CyberAgent_Id = 636 and Category_Id = 888) or --Garages
(CyberAgent_Id = 650 and Category_Id = 893) or --Liquor Liability
(CyberAgent_Id = 653 and Category_Id = 897) or --Products Liability
(CyberAgent_Id = 625 and Category_Id = 880) or --Buses: Charter & school
(CyberAgent_Id = 628 and Category_Id = 883) or --Railroads: Protective Liability
(CyberAgent_Id = 629 and Category_Id = 884) or --Railroads: Shortline
(CyberAgent_Id = 629 and Category_Id = 884)) and --Trucking Physical Damage
(Pagenumber = 5)
Here is the error I get:
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
Msg 264, Level 16, State 1, Line 2
Column name 'HTML' appears more than once in the result column list.
I'd appreciate any guidance for this...Thanks in advance.
October 25, 2011 at 10:17 am
You can't specify the same column more than once in a single update. Which one does it use as the new value???
To accomplish what you are trying you will have to do these in multiple updates or use nested replace.
HTML = Replace(Replace(cast(HTML as nvarchar(max)), 'Ralph Brown', 'John J. Gaskill'), 'John Gaskill', 'John J. Gaskill')
etc...
If this is a one time thing I would just do multiple updates because the nested replace with that many will get very confusing quickly.
_______________________________________________________________
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/
October 25, 2011 at 10:20 am
Hey Sean...thanks for helping me out! This is indeed a one time event. I will just use the several statement method. Thanks again!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply