如何在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

我有一个带有a列' a '的表格。有些行的A列有14位数字，有些行只有12位。我需要把所有的项转换成14位数。数据类型是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.

我想要一次更新所有的行(一个查询)，在第一个数字之前加上零，所以像012345678910这样的条目会变成00012345678910。

Is it possible to do it in one single query? Thanks

可以在一个查询中完成吗?谢谢

3 个解决方案

#1

This should do what you want:

这应该做你想做的:

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

#2

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

只需更新所有长度为12的行，并预置“00”

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

#3

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

#1