dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
4
share rss forum feed

jfmezei
Premium
join:2007-01-03
Pointe-Claire, QC
kudos:23
reply to cdru

Re: Indexing fields with multiple values

Thanks. That makes sense.

So, if one updates a record to remove 1 of 4 tags and add 2 more, what is the common way to handle this when writing the record back ? Delete all previous tags and recreate a new list of tags ? Or go through existing tags one by one, deciding if they stay or get deleted, and then add only those that were not present in the original record ?

(I know these questions sound like programing 101, but I am just trying to translate my old skills to SQL terminology and techniques).



cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

said by jfmezei:

So, if one updates a record to remove 1 of 4 tags and add 2 more, what is the common way to handle this when writing the record back ? Delete all previous tags and recreate a new list of tags ? Or go through existing tags one by one, deciding if they stay or get deleted, and then add only those that were not present in the original record ?

Depends on how you are maintaining the list. I personally would have a way to add mutiple (potentially) new values via a comma separated list, but existing records are deleted individually.

However you could always maintain them as just a CSV list. It would just be more work as you'd have to delete existing values that weren't in the updated list. The update process wouldn't be as atomic as what individualized deletes would be, or inserting a list individually.


drew
Automatic
Premium
join:2002-07-10
Port Orchard, WA
kudos:6
reply to jfmezei

DELETE FROM recipe_tags
WHERE tag_id IN (@TAG_IDs)
AND recipe_id = @RECIPE_ID
 

Provide the parameters and it's easy...

A similar INSERT INTO... would be simple.
--
flickr | 'Cause I've been waiting, all my life just waiting
For you to shine, shine your light on me


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

said by drew:

[code]
DELETE FROM recipe_tags
WHERE tag_id IN (@TAG_IDs)
AND recipe_id = @RECIPE_ID
[/code]

Provide the parameters and it's easy...

MS SQL won't allow this, as written. Passing in a CSV list as a parameter won't work unless you use a CTE or some other function to break the list into a rowset first.