CODEWITHSUNDEEP
mysql
Rabin Lama Dong
Rabin Lama Dong

Reputation: 2476

How to update table column using WHERE clause?

I have a table postcode. It has a column postcode which contains 4/5 digit integers (i.e. 66770, 66670, 3090, 6660). I want to run a query which prepends 0 to all the fields which contains 4 digit values (i.e. change '6660' to '06660'). I tried this query but it says 0 rows affected and no changes are made.

UPDATE postcode SET postcode = Concat('0', postcode) WHERE CHAR_LENGTH(postcode) = 4;

Did I do something wrong ?

Upvotes: 1

Views: 62

Answers (3)

Rabin Lama Dong
Rabin Lama Dong

Reputation: 2476

The answer is:

UPDATE postcode set postcode = CONCAT('0', postcode) where LENGTH(postcode)=4;

Upvotes: 1

R0th3n
R0th3n

Reputation: 31

If all your records are 4 or 5 you don't need the where clause and 

UPDATE postcode SET postcode = RPAD(TRIM(postcode), 5, '0')

Should do the work. However if you have records with 3 digits and you don't wanna update these you need to add the where to filter

UPDATE postcode 
SET postcode = LPAD(TRIM(postcode), 5, '0') 
WHERE CHAR_LENGTH(TRIM(postcode) = 4;

I am using TRIM() in case the postcode field had some empty space at the end.

Upvotes: 1

Svenmarim
Svenmarim

Reputation: 3735

Try this: UPDATE postcode SET postcode = REPLACE(postcode, N'____', 0);

Here i just replace everything with 4 characters to 0 (there are 4 underscores)

Upvotes: 2

Related Questions