CODEWITHSUNDEEP
sqlreplacesql-update
Matt Weick
Matt Weick

Reputation: 332

Update Records in SQL

I have database I can't figure out how to update a column because it's on a table that is generated per call. Here is the code to call the column in question:

select 
    a.itm_num as item_no,
    a.itm_proddesc as title,
    CAST(d.content as varchar(max)) as product_description
from item as a
join assignment as b on b.itm_id = a.itm_id
join attachment as c on c.att_id = b.att_id
join digitalassetcontent as d on d.content_key = c.content_key
where c.att_type like 'text%'

I need to update the product_description field. We have thousands of entrys in this column that have HTML coding in front of the actual text. I need to run a find and replace to remove the html coding before the text. The HTML code before each text is exactly the same, here is what needs to be removed: <STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>

I ran the following code with @Joel Coehoorn solution with the content key I need to update and it kicked back an error of 'Msg 4104, Level 16, State 1, Line 13 The multi-part identifier "d.content" could not be bound.'

select 
    a.itm_num as item_no,
    a.itm_proddesc as title,
    CAST(d.content as varchar(max)) as product_description
from item as a
join assignment as b on b.itm_id = a.itm_id
join attachment as c on c.att_id = b.att_id
join digitalassetcontent as d on d.content_key = c.content_key
where c.att_type like 'text%'

Update digitalassetcontent
set content = replace(CAST(content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')
where CAST(content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%' AND content_key = 'desc214974236480438500781058983745755010'

Thanks!

Matt

Upvotes: 0

Views: 89

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

That's just the digitalassetcontent.content field. You should be able to just act on the field directly. What is the actual type of that field?

Update digitalassetcontent
set content = replace(CAST(d.content as varchar(max)), '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>','')
where CAST(d.content as varchar(max)) like '<STYLE>H1{font-weight:bold}H1{font-size: 14pt}OL</STYLE><H1>Product Description</H1>%'

Upvotes: 1

Related Questions