Sorry for the duplicate msg. I forgot to state what version server I was running (next line) server version: 3.23.32 ----- Original Message ----- From: "Network" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 20, 2001 11:38 PM Subject: replace/insert into ... 150,000 -plus rows > 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
