CODEWITHSUNDEEP
mysqlsqldatabase
user1544337
user1544337

Reputation:

Adding a character to every row in a MySQL database

I have a table in which I inserted a large batch of records. All these records have a certain column in which one character is missing: they're missing a slash / at the beginning. Is there a way to add this character to the beginning of this column in every row, using purely SQL?

It wouldn't be a problem in PHP, but I'd like to know a solution in SQL. I know of the SQL UPDATE statement, of course, but I'm not sure how to let it rely on the current value. The best I can come up with is using a subquery:

UPDATE the_table t 
SET the_column = 
    CONCAT('/', SELECT the_column FROM the_table WHERE id=t.id)

Would this work, and is this the easiest solution? I'm on MySQL 5.5.31.

Upvotes: 1

Views: 5596

Answers (4)

adrCoder
adrCoder

Reputation: 3275

Have you tried this ?

UPDATE t SET column = Concat('/', column);

Upvotes: 2

Semyon Tikhonenko
Semyon Tikhonenko

Reputation: 4242

Try this

UPDATE table t SET column = CONCAT('/', column)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your version might work. But you really just want to do:

UPDATE the_table t 
    SET the_column = CONCAT('/', the_column)

Upvotes: 2

Jens
Jens

Reputation: 69440

Simply use this:

UPDATE the_table t 
SET the_column = 
    CONCAT('/', the_column )

Upvotes: 8

Related Questions