Update field with the longest varchar field

Question

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!

Answer 1

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;

Answer 2

You can use the function the SQL function DATALENGTH to get the longest value and use a CTE to get the biggest one

SELECT TOP 1 *
FROM
(
   SELECT options , (options ) AS DLength
   FROM ForrasReportsRicsiTeszt
) AS X
ORDER BY DLength DESC

In a similar way but not very optimal you can do this

UPDATE ForrasReportsRicsiTeszt
set options = REPLICATE(options, 4)
where options in(
--Only if options it's unique, if not need to return the ID
SELECT TOP 1 X.options
FROM
(
  SELECT options ,DATALENGTH(options ) AS DLength
  FROM ForrasReportsRicsiTeszt
) AS X
ORDER BY DLength DESC
)