Replace column data start from number in mysql

Question

i have a table i which there r 3 columns comp_no comp_area comp_post_code

now comp_area should have area of that place like , Toronto Vancouver, Winnipeg ...

But near about 2,290 entries r wrong and have comp_no in it like 17310 111 Ave NW, 205 Main St and so on. i want to remove these 2,290 entries and replace them with 0

I am using this to find these column

SELECT * 
FROM  `canada_database` 
WHERE comp_area !=  ''
AND comp_area NOT REGEXP  '^[[:alpha:]]'
ORDER BY slno ASC 
LIMIT 0 , 30

can it be done with mysql query

Jocelyn · Accepted Answer · 2013-04-24 10:40:31Z

1

UPDATE `canada_database` 
SET comp_area =  '0'
WHERE comp_area !=  ''
AND comp_area NOT REGEXP  '^[[:alpha:]]'

answered Apr 24, 2013 at 10:40

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Suthar · Accepted Answer · 2013-04-24 10:38:49Z

1

Try this query

UPDATE `canada_database` 
SET comp_area = 0
WHERE id IN (
    SELECT GROUP_CONCAT(id)
    FROM  `canada_database` 
    WHERE comp_area !=  ''
    AND comp_area NOT REGEXP  '^[[:alpha:]]'
)

answered Apr 24, 2013 at 10:38

Yogesh Suthar

30.5k18 gold badges75 silver badges100 bronze badges

Collectives™ on Stack Overflow

Replace column data start from number in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related