Reputation: 10604

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

Upvotes: 3

Answers (3)

Taylor Leese

Reputation: 52300

This should do what you want:

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

Upvotes: 5

Eddie

Reputation: 10138

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

Upvotes: 0

knittl

Reputation: 265171

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

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

Upvotes: 0

How to update multiple rows with different values in MySQL?

Answers (3)

Related Questions