The problem= The 'addy1' column or field (in attdata) is tainted with incorrect
data entries ... e.g. 'addy1' should only have addresses stored however, some
bright person(s) have managed to input company names in the address/addy1 field.
I'm presented the task of fixing this data before incorporating it into my
*mysql database*.
I have chosen the following method in an attempt to get the above done.
In my 'attdata' table I have the following fields:
id INT(9) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
comp VARCHAR(70) NULL,
addy1 VARCHAR(50) NULL,
-
In my 'biz' table I have these fields:
cid INT(9) NULL,
bname VARCHAR(70) NULL,
baddy VARCHAR(25) NULL,
populating biz.cid -and- biz.bname *from* attdata.id -and- attdata.comp using
the following syntax works;
insert into biz (cid, bname) select id, comp from attdata; <-- no need for
selective insert; data in these fields are *not* tainted.
(the above worked like as described)
-
Now the immediate problem. This third field attdata.addy1 has the wrong data
entered in in thousands of rows. e.g this is an address field, someone entered
company names in this field on certain rows. I resort to using regexp within a
where-clause to facilitate selectively extracting from attdata.addy1 then,
inserting/replacing those entries/addresses beginning with 0-9 into the
biz.baddy column. Below is the syntax I use and the results:
mysql> insert into biz (baddy) select addy1 from attdata where addy1 regexp
"^[0-9]";
Query OK, 173012 rows affected (23.70 sec)
Records: 173012 Duplicates: 0 Warnings: 0
Everything looks ok from this output but, when I query the biz.baddy... nothing
at all was inserted or replaced in to the biz.baddy column for any rows. I have
also tried other syntax variants such as:
mysql> insert biz (baddy) select addy1 from attdata where addy1 regexp "^[0-9]";
mysql> replace into biz (baddy) select addy1 from attdata where addy1 regexp
"^[0-9]";
mysql> replace biz (baddy) select addy1 from attdata where addy1 regexp
"^[0-9]";
-
I have also tried changing the default value of the column from NULL to 'not
null" then re-ran all the above syntax and still, I can *not* get the biz.baddy
to be replaced with the data extracted from attdata.addy1.
-
NOTE: all of the above happens when I first use the following syntax:
insert into biz (cid, bname) select id, comp from attdata;
-
NOTE-2: If I start with a completely empty biz table and issue either of the
following queries, all the data is transferred properly but, this would defeat
my purpose (remember) the addy1 field is tainted with company names in the
address field.
mysql> insert into biz (cid, bname, baddy) select id, comp, addy1 from attdata;
mysql> insert into biz (cid, bname, baddy) select id, comp, addy1 from attdata
where addy1 regexp "^[0-9]";
The cherry on top of this ice-cream cone is, update will fix my issue however I
have over 150,000 rows of data that are pretty much messed-up because of the
tainted 'addy1' field. Can you imagine the time and, possibly even more
mistakes that would take place if update had to be done on every row just to fix
this?
I'll not trust this task to anyone other than myself. Any Ideas on what I am
typing wrong within the insert/replace syntax shown above?
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
