CODEWITHSUNDEEP
mysqlsqlsql-updateconcatenation
steeped
steeped

Reputation: 2633

mySQL prepend text to column if not null

I just did a mass import of 200,000 items into the database, but forgot to add the directories under the image column. Is there a way to mass update the column and prepend the images directory? This is what I've been trying, but it doesn't work:

UPDATE wp_store_locator SET sl_image = CONCAT(  'http://website.com/wp-content/uploads/2015/06/', sl_image ) WHERE sl_image != NULL

This runs successfully, but doesn't alter the sl_image column.

Upvotes: 1

Views: 93

Answers (2)

Mureinik
Mureinik

Reputation: 312289

null is not a value - it's the lack thereof. Thus, it cannot be tested with the = or != operators, but needs to be checked with the is operator:

UPDATE wp_store_locator 
SET   sl_image = CONCAT('http://website.com/wp-content/uploads/2015/06/',
                        sl_image ) 
WHERE sl_image IS NOT NULL

Upvotes: 1

juergen d
juergen d

Reputation: 204924

Use the IS operator when comparing with null

UPDATE wp_store_locator 
SET sl_image = CONCAT('http://website.com/wp-content/uploads/2015/06/', sl_image ) 
WHERE sl_image is not NULL

Upvotes: 1

Related Questions