CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Mubeen
Mubeen

Reputation: 29

How to Update and Set column value using a sub-string inside a sub-query?

During altering the column size from MAX to 300 characters, I want to update all the values in the column to less than 300 characters, if the len() > 300.

UPDATE table_name 
SET comments = (SELECT SUBSTRING(comments, 1, 299) 
                FROM table_name 
                WHERE LEN(comments) > 300) 
WHERE LEN(comments ) > 300 
  AND person_id = '1234567890'

Any help, would be much appreciated.

Upvotes: 1

Views: 355

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

Don't use a subquery at all:

UPDATE table_name 
SET comments = SUBSTRING(comments, 1, 299) 
WHERE LEN(comments ) > 300 
  AND person_id = '1234567890'

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

this is needed to be done before alteration. Alteration can be done if the altered column does not have data larger than 300 characters long. Otherwise, SQL server Gives error.

Check This

Upvotes: 0

Related Questions