update question

Question

update question

scottlackey2001

Old Hand

Points: 330
More actions
November 3, 2016 at 7:05 am

#337088

here is that I want to do, in a single update statement
I have a columnname "foo". It may or may not contain a value. If it does, I wish to append a value with a comma separator. If not just a regular update
in other words, if blank or null, update tablename set foo = "newvalue"
if not null or blank, update tablename set foo = foo + ", newvalue"
Can this be done in a single UPDATE statement with some sort of IF conditional??

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23102 More actions · Answer 1

Yes this is possible, as demonstrated below. But, this is an extremely simplified example that may not be appropriate for your situation. If you post some sample data people will be able to give you a response that does exactly what you want.

CREATE TABLE #bar

(

Foo VARCHAR(10) NULL

)

INSERT INTO #bar VALUES

('Foo1'),(NULL),('Foo2'),(''),('Foo3')

SELECT * FROM #bar

UPDATE #bar

SETFoo = CASE WHEN Foo IS NULL THEN 'New'

WHEN Foo = '' THEN 'New'

ELSE 'foo' + ',' + 'New' END

SELECT * FROM #bar

DROP TABLE #bar

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

David Burrows SSC Guru Points: 65144 More actions · Answer 2

COALESCE(NULLIF(foo,'')+','+newvalue,newvalue)

Far away is close at hand in the images of elsewhere.
Anon.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

I avoid the ISNULL "tricks" when I can in favor of straightforward code:

UPDATE table_name

SET Foo = CASE WHEN Foo > '' THEN ', ' ELSE '' END + 'newvalue'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 77014 More actions · Answer 4

ScottPletcher (11/3/2016)
I avoid the ISNULL "tricks" when I can in favor of straightforward code:
UPDATE table_name
SET Foo = CASE WHEN Foo > '' THEN ', ' ELSE '' END + 'newvalue'

I think you meant

UPDATE table_name

SET Foo = CASE WHEN Foo > '' THEN Foo + ', ' ELSE '' END + 'newvalue'

If you hate the ISNULL/NULLIF then you'll really hate this version. 😀

UPDATE table_name

SET Foo = ( SELECT Foo + ',', 'newvalue' FOR XML PATH(''), TYPE).value('.', 'VARCHAR(500)')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

drew.allen (11/3/2016)
ScottPletcher (11/3/2016)
I avoid the ISNULL "tricks" when I can in favor of straightforward code:
UPDATE table_name
SET Foo = CASE WHEN Foo > '' THEN ', ' ELSE '' END + 'newvalue'
I think you meant
UPDATE table_name
SET Foo = CASE WHEN Foo > '' THEN Foo + ', ' ELSE '' END + 'newvalue'
Drew

Quite right. That code's logic is clearer to me: if Foo already contains a value, use it followed by a comma and space, then add the new value; if it doesn't, just use the new value.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".