How to update multiple rows with different values in MySQL?

Question

I have a table with a column 'A'. Some rows have 14 digits for the column 'A' and some have only 12. I need to transform all the entries to 14 digits. The datatype is varchar I would like to update all the rows at once (one query), adding zeros before the first digit, so an entry like 012345678910 would become 00012345678910. Is it possible to do it in one single query? Thanks

Accepted Answer

This should do what you want:

```
UPDATE your_table SET column_name = LPAD(column_name, 14, "0")
WHERE LENGTH(column_name) < 14
```

How to update multiple rows with different values in MySQL?

Solution

Problem