19930

change a field and port mysql table data via script ?

mysql> desc oldtable; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | uid | int(11) | NO | PRI | NULL | auto_increment | | active | char(1) | NO | | NULL | | +---------------+--------------+------+-----+---------+----------------+ mysql> desc newtable; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | uid | int(11) | NO | PRI | NULL | auto_increment | | active | tinyint(1) | NO | | 0 | | +------------+--------------+------+-----+---------+----------------+

I would like to port data (dump) from oldtable into newtable. One issue is, earlier the table used char(1) for active which stores value either 'Y' or 'N'. Now the newtable stores it as int either 1 or 0.

How can i fix this before porting data? Should I use shell script for such fix & porting ? Any sample scripts or tips :)

Answer1:

INSERT INTO newtable SELECT uid,IF(active='Y',1,0) as active FROM oldtable

should do the trick

Answer2:

INSERT INTO newtable (uid, active) SELECT uid, IF(active='Y', 1, 0) AS active FROM oldtable

docs for this syntax here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

Answer3:

Joking version:

INSERT INTO newtable SELECT uid,FIELD(active,'Y') as active FROM oldtable

Recommend

  • Add row and fill value in dataGridView C#
  • create jquery dialog from javascript object
  • Crystal reports connecting to Azure SQL database. The table x could not be found
  • LINQ query with a generic table
  • Limit user access to user identified by IP address only 5 times a day
  • Can mySQL define specific columns as UNIQUE based on another column?
  • Split results from DB into “chunks” of 10
  • mysql not unique auto increment, primary key two fields
  • 2 count(*)+group by+having+join
  • What is the best data type to store boolean values in a database
  • PostgreSQL primary key auto increment crashes in C++
  • How to find data from last week in MySQL
  • Can I use jquery to blank textarea fields or ajax like input boxes?
  • CORS with socket.io
  • Examples of how to a STS in .Net 4.5 using WCF
  • Is it possible to define rest argument in OCaml?
  • Prevent Tomcat from caching request during starup
  • android google indoor map
  • Access Android Market through SSH tunnel
  • Visual Studio 2010 debugger build correctly - compiler pdb and linker pdb not in synch?
  • Is there a perl module to validate passwords stored in “{crypt}hashedpassword” “{ssha}hashedpassword
  • How to run “Deployd” on port 80 instead of port 5000 in webserver.
  • java inputstream
  • Custom Tabgroup Appcelerator
  • AES padding and writing the ciphertext to a disk file
  • Where to put my custom functions in Wordpress?
  • Convert array of 8 bytes to signed long in C++
  • Delete MySQLi record without showing the id in the URL
  • Numpy divide by zero. Why?
  • Unanticipated behavior
  • Comma separated Values
  • Linker errors when using intrinsic function via function pointer
  • Trying to get generic when generic is not available
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • embed rChart in Markdown
  • LevelDB C iterator
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • Converting MP3 duration time