Help with SQL-transact! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with SQL-transact!

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-11 : 15:01:24

I would like to add the specific update method in my web project. I have a keyWords cell in Images table where users images keywords are stored. How can I add the new keywords into this cell if I allredy have a keywords in table. For example: I have an image which has 3 keywords - tree, sun & beach... as a nvarchar parameter I have a new keyword: "boat"... what is the secret in the procedure, to add this new parameter infront of the "tree", "sun" & "beach" to get boat, tree, sun, beach?

This one deletes an old values and adds new:
ALTER PROCEDURE [dbo].[tbh_Portfolios_Images_UpdateKeyWords]
(
@KeyWords nvarchar(100),
@ImageID int
)
AS

UPDATE tbh_Portfolios_Images
SET KeyWords = KeyWords + @KeyWords
WHERE ImageID = @ImageID

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-10-11 : 15:19:42

UPDATE tbh_Portfolios_Images
SET KeyWords = @KeyWords + ','+KeyWords
WHERE ImageID = @ImageID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-11 : 16:46:19

And what is your main reason to have a denormalized table?

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-11 : 17:21:22

quote:
Originally posted by Peso

And what is your main reason to have a denormalized table?

E 12°55'05.63"
N 56°04'39.26"

What do You mean Peso?

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-11 : 17:24:53

quote:
Originally posted by afrika

UPDATE tbh_Portfolios_Images
SET KeyWords = @KeyWords + ','+KeyWords
WHERE ImageID = @ImageID

This method also deletes the old values and sets the new!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 00:57:44

UPDATE tbh_Portfolios_Images
SET KeyWords = ltrim(rtrim(@KeyWords)) + ',' + ltrim(rtrim(KeyWords))
WHERE ImageID = @ImageID

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 00:58:51

quote:
Originally posted by krainov

What do You mean Peso?

See http://www.datamodel.org/NormalizationRules.html
for more explanation

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-12 : 14:22:16

quote:
Originally posted by Peso

quote:
Originally posted by krainov

What do You mean Peso?
See http://www.datamodel.org/NormalizationRules.html
for more explanation

E 12°55'05.63"
N 56°04'39.26"

Peso! Where do I go wrong? Do you offer me to make a separated table for keywords or what? I'm new into sql-business.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 14:33:25

that would be much better approach. Make a table with keyword id,keywordname and imageid to accomodate each keywords that are part of image. imageid will have foreign key relatiuonship with imageID column of tbh_Portfolios_Images

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-12 : 14:34:15

quote:
Originally posted by Peso

UPDATE tbh_Portfolios_Images
SET KeyWords = ltrim(rtrim(@KeyWords)) + ',' + ltrim(rtrim(KeyWords))
WHERE ImageID = @ImageID

E 12°55'05.63"
N 56°04'39.26"

Peso! This method is also deletes the old values!

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-10-12 : 14:38:19

quote:
Originally posted by visakh16

that would be much better approach. Make a table with keyword id,keywordname and imageid to accomodate each keywords that are part of image. imageid will have foreign key relatiuonship with imageID column of tbh_Portfolios_Images

Visakh! And what is the reason to keep an each keyword in separeted cell? Why it is not good to keep it as a string (2-5-10 keywords) in the same table? This will lead me to an additional logics in code.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 15:12:33

quote:
Originally posted by krainov

Peso! This method is also deletes the old values!

No it doesn't.
Unless KeyWords column is designed to only hold a very small number of characters.
But then you would have got an error "String or binary data truncated". If you have that error message and don't tell us, then who is to blame?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-12 : 15:20:49

[code]DECLARE @Sample TABLE
(
PicID SMALLINT,
TagLine VARCHAR(500)
)

INSERT @Sample
SELECT 1, 'Tag two, Tag three, Tag four and Tag five' UNION ALL
SELECT 2, 'This will work! Peso gets the cheese...'

SELECT *
FROM @Sample

DECLARE @Tag VARCHAR(50)

SET @Tag = 'Tag one'

UPDATE @Sample
SET TagLine = @Tag + ', ' + TagLine
WHERE PicID = 1

SELECT *
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 00:51:03

quote:
Originally posted by krainov

quote:
Originally posted by visakh16

that would be much better approach. Make a table with keyword id,keywordname and imageid to accomodate each keywords that are part of image. imageid will have foreign key relatiuonship with imageID column of tbh_Portfolios_Images

Visakh! And what is the reason to keep an each keyword in separeted cell? Why it is not good to keep it as a string (2-5-10 keywords) in the same table? This will lead me to an additional logics in code.

Did you read the link Peso posted on normalization principles?
its against first normal form.The manipulation of string will also become difficult just in case you need to update keyword value or even find out what all keywords are used by more than one events,...
so its better to keep it in seperate table with keywordid,name & eventid as columns

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources