Append a string to a columns value

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

SQL Server 2000 Forums

SQL Server Development (2000)

Append a string to a columns value

Author

Topic

derekg
Starting Member

1 Post

Posted - 2005-11-23 : 06:51:33

Hi all
I am trying to do the following and just cant work it out!

1) Get all rows meeting my where criteria (using a subquery)
2) Update all those rows with their original value and a string appended (the string is '474,')

Here is my code so far:
UPDATE content_attribute
SET
cat_value = '474,' + (SELECT DISTINCT CAST(cat_value AS varchar) FROM
dbo.content_attribute CA
WHERE
CA.cat_civ_cit_id IN
(
SELECT DISTINCT cit_parent_cit_id
FROM contentItem CI
WHERE
cit_id IN
(
SELECT
DISTINCT cat_civ_cit_id
FROM content_attribute
WHERE
cat_value LIKE '%102%'
AND
cat_ade_id = 379
)
AND
CI.cit_parent_cit_id IS NOT NULL
AND
CI.cit_parent_cit_id < 5000
)
AND
CA.cat_ade_id = 373
AND CA.cat_civ_version = (SELECT MAX(cat_civ_version)AS civMax FROM dbo.content_attribute CAMax WHERE cat_civ_cit_id = CA.cat_civ_cit_id AND cat_ade_id = 373)
)

WHERE
cat_ade_id = 373
AND
cat_civ_cit_id = CA.cat_civ_cit_id --where it breaks

AND cat_value NOT LIKE '%474%'

Thanks
Derek

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:35:56

Are u getting any errors .. ???

and i guess you should do somthing like this ..

UPDATE content_attribute
SET
cat_value = '474,' + CAST(CA.cat_value AS varchar)
FROM
dbo.content_attribute CA
WHERE
CA.cat_civ_cit_id IN
(
SELECT DISTINCT cit_parent_cit_id
FROM contentItem CI
WHERE
cit_id IN
(
SELECT
DISTINCT cat_civ_cit_id
FROM content_attribute
WHERE
cat_value LIKE '%102%'
AND
cat_ade_id = 379
)
AND
CI.cit_parent_cit_id IS NOT NULL
AND
CI.cit_parent_cit_id < 5000
)
AND
CA.cat_ade_id = 373
AND CA.cat_civ_version = (SELECT MAX(cat_civ_version)AS civMax FROM dbo.content_attribute CAMax WHERE cat_civ_cit_id = CA.cat_civ_cit_id AND cat_ade_id = 373)
)

WHERE
cat_ade_id = 373
AND
cat_civ_cit_id = CA.cat_civ_cit_id --where it breaks

AND cat_value NOT LIKE '%474%'

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources