I think you can use update replace. UPDATE table SET column=REPLACE(column,'$','');
Kyong On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg <kc0...@yahoo.com> wrote: > Hello, > > I am importing CSV data from a proprietary database into a table in MySQL. > Due to a flaw in the proprietary software's export tool, currency values > (floats) are always exported with a '$' prefixed to them. This causes a > problem where the matching float field in the MySQL table being set to zero > (or not set at all) after import. > > As a solution to getting a complete import, I modified the data type of the > field in MySQL from float to varchar(8), so now the data is present in the > table. > > I am faced with the problem of removing the '$' from the string. > > I can filter out the '$' by doing a string manipulation, > > SELECT MID((SELECT `imported_data`.`PartPrice` FROM `imported_data` WHERE > `imported_data`.`PartNumber`='1') FROM 2); > > I can change the value of a record from $100 to 100 by hand. > > UPDATE `imported_data` SET `imported_data`.`PartPrice`='100' WHERE > `imported_data`.`ParttNumber`='49152'; > > And thus tried, > > UPDATE `imported_data` SET `imported_data`.`PartPrice`=(SELECT MID((SELECT > `imported_data`.`PartPrice` FROM `imported_data` WHERE > `imported_data`.`PartNumber`='49152') FROM 2);) WHERE > `imported_data`.`PartNumber`='49152'; > > It was a nice try, but found out from MySQL that "You can't specify target > table 'imported_data' for update in FROM clause" and discovered that it > really looks like that I cannot write data to a table while a nested query is > reading the same location. > > I could create a new field and insert into that instead of updating. > > But, regardless of the approach, I would still have to execute this statement > some 8,000 times. Once for each part number I have. Putting something like > this in a for-loop almost feels like I am avoiding a feature of convenience > that I am not aware of. > > There really must be an easier way. Can anybody help me with a more elegant > solution? (BTW, I have been explicitly forbidden from doing a search and > replace on '$' with the CSV file that got exported) > > > Thank you for assisting me and your support of a fine database software > package! > > > Timothy Legg > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
