Nedd to use this select to update a field in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nedd to use this select to update a field in table

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-01 : 04:05:49

Hi!
I have problem writing a query to update textSum field with the values from the inner select in the case select below.
Seems that I cannot get it to work with a self join based on id.

Thanks in advance!

The output should be:
id textSum
1, a1/a2/a3/a4/a5/a6
2, b1/b2/b3/b4/b5/b6
3, c1/c2/c3/c4/c5/c6

create table #table1
(
id int identity(1,1),
text1 varchar(10) null,
text2 varchar(10) null,
text3 varchar(10) null,
text4 varchar(10) null,
text5 varchar(10) null,
text6 varchar(10) null,
testSum varchar(100) null
)

insert into #table1(text1, text2, text3, text4, text5, text6) values('a1','a2','a3','a4','a5','a6')
insert into #table1(text1, text2, text3, text4, text5, text6) values('b1','b2','b3','b4','b5','b6')
insert into #table1(text1, text2, text3, text4, text5, text6) values('c1','c2','c3','c4','c5','c6')

SELECT id,
CASE WHEN textSum = '' THEN '' ELSE left(textSum, LEN(textSum) - 1) END
FROM
(
SELECT id,
coalesce(NULLIF(text1, '') + '/', '') +
coalesce(NULLIF(text2, '') + '/', '') +
coalesce(NULLIF(text3, '') + '/', '') +
coalesce(NULLIF(text4, '') + '/', '') +
coalesce(NULLIF(text5, '') + '/', '') +
coalesce(NULLIF(text6, '') + '/', '') AS textSum
FROM #table1
) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 04:10:45

Why do you want to update with the table?
You can derive it using SELECT Statement like you used

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-01 : 04:14:16

you can do this if you are using SQL Server 2005


UPDATE	t
SET	testSum = CASE WHEN new_testSum= '' THEN '' ELSE left(new_testSum, LEN(new_testSum) - 1) END
FROM
(
	SELECT 	id,
		coalesce(NULLIF(text1, '') + '/', '') +
		coalesce(NULLIF(text2, '') + '/', '') +
		coalesce(NULLIF(text3, '') + '/', '') +
		coalesce(NULLIF(text4, '') + '/', '') +
		coalesce(NULLIF(text5, '') + '/', '') +
		coalesce(NULLIF(text6, '') + '/', '') AS new_testSum,
		testSum
	FROM #table1
) t

edit : typo error

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-01 : 04:17:32

or use computed column

create table #table1
(
id int identity(1,1),
text1 varchar(10) null,
text2 varchar(10) null,
text3 varchar(10) null,
text4 varchar(10) null,
text5 varchar(10) null,
text6 varchar(10) null,
testSum as 
	coalesce(NULLIF(text1, '') + '/', '') + 
	coalesce(NULLIF(text2, '') + '/', '') + 
	coalesce(NULLIF(text3, '') + '/', '') + 
	coalesce(NULLIF(text4, '') + '/', '') + 
	coalesce(NULLIF(text5, '') + '/', '') + 
	coalesce(NULLIF(text6, '') + '/', '')
)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-01 : 04:22:32

or in SQL Server 2000 use INNER JOIN of the original table to the derived table

UPDATE	t
SET	testSum = CASE WHEN new_testSum = '' THEN '' ELSE left(new_testSum, LEN(new_testSum) - 1) END
FROM	#table1 t
INNER JOIN 
(
	SELECT 	id,
		coalesce(NULLIF(text1, '') + '/', '') +
		coalesce(NULLIF(text2, '') + '/', '') +
		coalesce(NULLIF(text3, '') + '/', '') +
		coalesce(NULLIF(text4, '') + '/', '') +
		coalesce(NULLIF(text5, '') + '/', '') +
		coalesce(NULLIF(text6, '') + '/', '') AS new_testSum
	FROM #table1
) n	ON	t.id	= n.id

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources