Update field with the longest varchar field

querywhere

I need to update on of the varchar fields in a table, but only the record that is the longest varchar (has the most data in it.). I could easily find it with this query:

SELECT TOP(1) options
FROM ForrasReportsRicsiTeszt
ORDER BY LEN(options) DESC

Now I need to update it, but I am quite unexperienced with sql. This is a 2005 ms sql server.

my query for updating:

  UPDATE ForrasReportsRicsiTeszt
  SET options = REPLICATE(options, 4)
  WHERE TOP(1) ORDER BY LEN(options) DESC

This gives me an error to TOP(1) which was quite expected. I just don't know the right way to write this query, how could I get to update only the longest varchar field? The one I've managed to get with my select query. Sadly there is no primary key that I could use in my WHERE.

Any help would be much appreciated!

Best Answer

Using the MAX function you could write the following to obtain the entry with the longest varchar

SELECT 
  MAX(LEN(options))
FROM 
  ForrasReportsRicsiTeszt;

This can then be combined with an UPDATE statement to give

UPDATE ForrasReportsRicsiTeszt
SET
   options = REPLICATE(options, 4)
WHERE
   LEN(options) = (  SELECT 
                     MAX(LEN(options))
                   FROM 
                    ForrasReportsRicsiTeszt
               );

Alternatively you could use a Common Table Expression (CTE).

;WITH MyTestCTE ( LengthLogngestOption)
AS (
    SELECT 
      MAX(LEN(options))
    FROM 
      ForrasReportsRicsiTeszt
   )
UPDATE FRRT
   SET FRRT.Options = REPLICATE(options,4)
FROM
   ForrasReportsRicsiTeszt FRRT
JOIN
   MyTestCTE MTCTE
ON
LEN(FRRT.OPTIONS) = MTCTE.LengthLogngestOption;
Related Question