How to update multiple rows with different values in MySQL?

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

Answers


This should do what you want:

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

just update all rows which length is 12, and prepend '00'

UPDATE `table`
SET `col` = '00'+`col`
WHERE LENGTH(`col`) = 12

update table1 set columnA=concat('00',columnA) where char_length(columnA)=12

Need Your Help

CSS 3d rotation doesn't work with column layout on Chrome

javascript html5 css3 css-transforms css-multicolumn-layout

I need to control the transform of some divs clicking in a html node. When these divs are with a display "inline-block" it works ok, but if I wrap them in a column layout with css3, they doesn't wo...

jQuery selector for the label of a checkbox

jquery checkbox jquery-selectors

&lt;input type="checkbox" name="filter" id="comedyclubs"/&gt;