SQL Server update column with value from another column

Question

I have two SQL tables:

User:

UserId (nvarchar)
Tags (nvarchar)

Profile:

UserId (nvarchar)
Tags (nvarchar)

Example:

User table:

UserId      Tags
----------------------------------
1           tag1,tag2,tag3,tag4
2           tag1,tag2
3           tag1
4           tag2,tag3,tag4

after the Profile table tags should be

Profile table:

UserId      Tags
-------------------------------------
1           ["tag1","tag2","tag3","tag4"]
2           ["tag1","tag2"]
3           ["tag1"]
4           ["tag2","tag3","tag4"]

Currently the user tags are stored in User table in the Tags column like this, separated by comma: tag1,tag2,tag3,tag4. There can be also one single tag listed: tag1 (no comma present).

I need to "move/copy" those tags in the Profile table Tags column in the following format (string array): ["tag1","tag2","tag3","tag4"]

So basically in my update statement bellow, second line: SET Profile.Tags = UA.TAGS, UA.TAGS value should be converted into an array of strings.

UPDATE Profile
SET Profile.Tags = UA.TAGS (UA.TAGS value should be an array) 
FROM Profile UP INNER JOIN User UA ON UP.UserId = UA.UserId

Any ideas how I can achieve that?

You should never ever store multiple values in a single column - this violates even the first normal form of database design. Since you're using a relational database - use the relational capabilities of it - define a separate table Tags for the tags, and a "link" table between either User and Tags, or Profile and Tags ... — marc_s
– marc_s, Commented Oct 26, 2016 at 5:11

Hadi · Accepted Answer · 2016-10-25 22:17:56Z

2

UPDATE Profile
SET Profile.Tags = '["'+ REPLACE(UA.TAGS,',','","') + '"]'
FROM Profile UP
INNER JOIN User UA
on UP.UserId = UA.UserId

answered Oct 25, 2016 at 22:17

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server update column with value from another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related