0

I have a table where I need to insert a '*' sign before a lot of names. My table looks like this now:

id,name

1,cake
2,car
3,mountain

And it should be:

id,name

1,*cake
2,car
3,*mountain

The name column is nvarchar. I have the ids in a csv file. I thought I could make the update statement with concat and a where clause with the IDs, but I can't get it to work.

I tried

UPDATE table
SET    NAME = concat('*', NAME)
WHERE  id = '1'
        OR id = '2' 

But got

Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized built-in function name

5
  • Get the IDs into a temporary table tempIDs(id), use then UPDATE my_table SET name=concat('*', name) WHERE id IN (SELECT id FROM tempIDs)
    – joanolo
    Jan 30, 2017 at 7:51
  • update table set name = concat('*',name) where id = '1' or id = '2' Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized built-in function name.
    – mgr33n
    Jan 30, 2017 at 8:17
  • Its and MSSQL 2005 instance :(
    – mgr33n
    Jan 30, 2017 at 8:19
  • As @joanolo suggest import whole csv file into a temporary table, and then update the related records.
    – McNets
    Jan 30, 2017 at 8:22
  • Thanks, I got it to work. Used the + sign in instead of the concat function.
    – mgr33n
    Jan 30, 2017 at 9:12

1 Answer 1

1
Begin Tran
    Create Table #TEMP(id INT,name NVARCHAR(50))

        INSERT INTO #TEMP
        SELECT 1,'cake' UNION ALL
        SELECT 2,'car' UNION ALL
        SELECT 3,'mountain'

        UPDATE #TEMP
        SET    NAME = +'*'+ NAME
        WHERE  id = '1' OR id = '2' 

        Select * from #TEMP

ROLLBACK TRAN
1
  • CONCAT Is Not A Build Function for SQL SERVER 2005 & 2008 But It is Available in Updated Version Of SQL Server like 2012,2014,2016 Jan 31, 2017 at 6:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.