CODEWITHSUNDEEP
sql-servert-sql
SBB
SBB

Reputation: 8970

TSQL Append To Row

I have a table that contains account numbers and tags (key words). My query checks to see if that account exists and if it does, it needs to append the new keywords to what already exits.

If the account doesn't exit, it just does a simple insert.

My struggle is getting the current Tags from the IF Exists clause and appending the new data to in it in the update statement. Any ideas?

BEGIN
SET NOCOUNT ON;

IF EXISTS (SELECT id, tags FROM AccountLogAccounts WHERE account = @account)
BEGIN
    UPDATE AccountLogAccounts 
    SET tags = (
        SELECT tags 
        FROM AccountLogAccounts
        WHERE account = @account
    ) + ',' @tags --This doesn't work :)
    WHERE account = @account
END
ELSE
    BEGIN
         INSERT INTO AccountLogAccounts (
            account,
            location,
            tags,
            whoAdded,
            whenAdded
        ) VALUES (
            @account,
            @location,
            @tags,
            @ntid,
            GETDATE()
        )
            END
END

Upvotes: 0

Views: 84

Answers (1)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Instead of...

UPDATE AccountLogAccounts 
    SET tags = (
        SELECT tags 
        FROM AccountLogAccounts
        WHERE account = @account
    ) + ',' @tags --This doesn't work :)
    WHERE account = @account

...try...

UPDATE AccountLogAccounts 
    SET tags = tags + ',' + @tags
    WHERE account = @account

EDIT

Here's a little demo (untested, but should work):

CREATE PROCEDURE UpdateMyText
    @account int,
    @tags nvarchar(max)
AS
UPDATE AccountLogAccounts 
    SET tags = CAST(tags as nvarchar(max)) + ',' + @tags
    WHERE account = @account
GO

EXEC UpdateMyText 12345, 'abc'
GO

Upvotes: 2

Related Questions