Untitled

combine the content of two seperate queries into one and update

UPDATE Products_Joined SET TechSpecs = 'var srating_'

+ Products_Joined.ProductCode + '="' + CAST(Reviews.AverageRating AS VARCHAR) + '";var rcount_' + Products_Joined.ProductCode + '="' + CAST(Reviews.TotalRatings AS VARCHAR) + '";'

 

FROM Products_Joined LEFT OUTER JOIN (

SELECT Reviews.ProductCode, AVG(Reviews.Rate) AS AverageRating, COUNT(Reviews.ReviewID) AS TotalRatings

FROM Reviews

GROUP BY Reviews.ProductCode

) AS Reviews ON Products_Joined.ProductCode = Reviews.ProductCode;

       

DECLARE @OpenTag varchar(100)

SET @OpenTag = '<script type="text/javascript" language="javascript">'

 

UPDATE Products_Joined

SET TechSpecs  = STUFF(CAST(TechSpecs AS VARCHAR(MAX)) ,

                   CHARINDEX(@OpenTag, CAST(TechSpecs AS VARCHAR(MAX)) ,1)  + LEN(@OpenTag),

                        CHARINDEX('</script>',CAST(TechSpecs AS VARCHAR(MAX)) ,1)-(CHARINDEX(@OpenTag, CAST(TechSpecs AS VARCHAR(MAX)) ,1)  + LEN(@OpenTag)),

                   '*****HERE IS WHERE I WANT TO PUT THE FIELD FROM THE FIRST SQL QUERY*******')

WHERE TechSpecs LIKE '%<script type="text/javascript" language="javascript">%</script>'