Writing An UPDATE statement using CONCAT

Question

I have an update to make through PMA. It seems simple but might not be. I have attached an image of the DB table and relevant fields.

enter image description here This is a DB table with some 90000 records. Some of the records in the "alias" column are exactly the result I need. For example, in the image, id's 743971, 743961 and 743951 are perfect. I am looking to update the 'alias' column with the matching number from the ID column, joined by a hyphen, and in lowercase. For example - in the image..the second record needs an alias update from just "ponder" to become "ponder-743981".

Is there a way to do an UPDATE with CONCAT, that can ignore the correct records, update the incorrect ones. (Ignore the first ID in that image, no alias records are blank.) Thanks for any help you can give!

There is a way,but thats a really bad way to store data in a db. — Mihai
– Mihai, Commented Sep 26, 2014 at 21:40

FuzzyTree · Accepted Answer · 2014-09-26 21:50:44Z

1

This query will only update incorrect alias columns

update mytable set alias = concat(lower(replace(imgtitle,' ','-')),'-',id)
where alias <> concat(lower(replace(imgtitle,' ','-')),'-',id)

but do you need to save the alias column at all?

select * , concat(lower(replace(imgtitle,' ','-')),'-',id) alias
from mytable

edited Sep 26, 2014 at 21:50

answered Sep 26, 2014 at 21:42

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

decaye Over a year ago

Thanks, Fuzzy Tree, that works perfectly, for some of the fields. except as I look through the imgtitle column, there are ampersands, commas, etc. Instead of replacing all those, is there a way we can change your query to just simply append "-" and "id" to the end of whatever data is already in the alias field, except for those that already match that pattern, of course. ?

FuzzyTree Over a year ago

@decaye change the set part to set alias = concat(alias,'-',id)

Collectives™ on Stack Overflow

Writing An UPDATE statement using CONCAT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related