0

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!

6
  • 1
    Why are you using top(1), you can simply get max function. And for update you can say where options = (select max(LEN(options) from ForrasReportsRicsiTeszt) Feb 2, 2021 at 12:20
  • It makes sense, I am still learning sql. I tried to edit the select according to your comment which turned out as this: SELECT options FROM ForrasReportsRicsiTeszt WHERE max(LEN(options)) = LEN(options) but my WHERE seems to be wrong. I've tried with SELECT max(len(options)) FROM ForrasReportsRicsiTeszt But that just returns the length of the field not the actual field As for the Update: UPDATE ForrasReportsRicsiTeszt SET options = REPLICATE(options, 4) where options = (select max(LEN(options) from ForrasReportsRicsiTeszt) Syntax error near from
    – 66Gramms
    Feb 2, 2021 at 12:33
  • But this seems to be the right track, maybe you could turn this into an answer?
    – 66Gramms
    Feb 2, 2021 at 12:34
  • Please edit your question with exact query and update statement. I think you are using max in the where clause at left hand side. You should be using it in the right side for which you want to update the value. Feb 2, 2021 at 12:36
  • 1
    Glad that your issue is solved :) Feb 2, 2021 at 12:45

2 Answers 2

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;
2
  • Thank you, this is correct, I'd like to ask how could I modify SELECT MAX(LEN(options)) FROM ForrasReportsRicsiTeszt; to return the value of the field instead of it's lengrh? I am asking this so I can make better SELECTs in the future and drop using TOP()
    – 66Gramms
    Feb 2, 2021 at 12:46
  • 1
    You can change your select query to remove len function, you could just run - select options from ForrasReportsRicsiTeszt. If you wish to get max value then use the max function - select max(options) from ForrasReportsRicsiTeszt Feb 2, 2021 at 13:09
1

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.