CODEWITHSUNDEEP
mysql
huondui
huondui

Reputation: 433

MySQL - fixing consistency Problems in URL column

I have a column called url in my database. When creating the tuples, I made a mistake that created an inconsistency.

Half of my data is saved as ../api/data/image.png, the other half is saved as ./data/image.png

| id     |     url          |
|    ... | ../api/data/image
|    ... | ../api/data/image
|    ... | ./data/image

I want every url in the column to look like /api/data/somefile_or_directory what would be the SQL Code for that?

Upvotes: 0

Views: 38

Answers (2)

spencer7593
spencer7593

Reputation: 108420

test expressions in a SELECT statement before incorporating them into an UPDATE statement. e.g.

SELECT t.url 
     , IF(t.url LIKE './data/%', CONCAT('../api/data/',SUBSTR(t.url,8)), t.url)  AS new_url
  FROM ( SELECT '../api/data/image' AS url 
         UNION ALL SELECT './data/foo' 
         UNION ALL SELECT './data'
         UNION ALL SELECT 'foo'
       ) t

results:

url                new_url 
-----------------  -----------------
../api/data/image  ../api/data/image
./data/foo         ../api/data/foo
./data             ./data
foo                foo

when we have an expression tested, and it gives the results we need, we can use it in an UPDATE statement

UPDATE mytable t 
   SET t.url = IF(t.url LIKE './data/%', CONCAT('../api/data/',SUBSTR(t.url,8)), t.url)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You could try a simple replacement:

UPDATE yourTable
SET url = REPLACE(url, "/data/", "/api/data/")
WHERE url NOT LIKE '%/api/%';

Demo

Upvotes: 2

Related Questions