CODEWITHSUNDEEP
sqlpostgresql
Tales
Tales

Reputation: 1923

Update column in postgresql

I found out that I have a character varaying column with mistakes in a database with over 4 millon records. It contains numbers. Each number has to have 12 digits, but for some reason a lot of those numbers ended up having 10 digits. The good news is that the only thing I have to do, is prepend '55' to each cell that only has 10 digits and starts with the number '22', leaving the ones with 12 digits untouched.

My objective is this:

UPDATE 
  table 
SET 
  column = CONCAT( '55', column ) 
WHERE 
  LENGTH( column ) = 10 AND column LIKE( '22%');

I am thinking of using this:

UPDATE 
  telephones 
SET 
  telephone_number = CONCAT( '55', telephone_number ) 
WHERE 
  LENGTH( telephone_number ) = 10 AND telephone_number LIKE( '22%');

Am I doing it right? If not, what would be the correct way to do it

What if instead of a string the numbers were stored as big int, same rules apply, it is still 10 digits long which means the number is lower than 3.000.000.000 and bigger than 2.000.000.000? and they all need to be the same number starting with 55

Upvotes: 1

Views: 160

Answers (1)

Austin Mullins
Austin Mullins

Reputation: 7427

The answer is: yes, that's right. You can play around with a sample database here on SQL Fiddle. That one uses the BIGINT type. Also see this one by @gmm, which uses the VARCHAR form. Both work just like you've described them using your original syntax.

Upvotes: 1

Related Questions