Jump to content


Photo

Update Query


  • Please log in to reply
2 replies to this topic

#1 ICEcoffee

ICEcoffee
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 29 June 2006 - 11:47 AM

Hi all

I have imported a table from an ODBC database into MySQL, but all the data in all the columns are surounded by quotation marks.

Can anyone tell me how to write a query I can execute, to update all fields, to delete said quotation marks.


Thanks for any help, it will save me hours of work.

#2 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 30 June 2006 - 06:20 PM

Do you have a "dumped" database in a text file?  Can you write a perl script (w/ a regular expression) to excise the quotes?  (Then reimport the data....)

Actually this might be easier...:

If you already have the data in the database, you can use something like the following:
UPDATE mytable SET mytextcolumn1=TRIM(BOTH '"' FROM mytextcolumn1),
mytextcolumn2=TRIM(BOTH '"' FROM mytextcolumn2);

Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 01 July 2006 - 12:40 AM

Dump the data from ODBC DB into a csv file.

Use Mysql LOAD DATA INFILE to load the data and specify that quotes are used.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users