create table tablename_new like tablename; -> this will copy the structure… insert into tablename_new select * from tablename; -> this would copy all the data
Tag Archives: mysql
Find and replace in MYSQL
Updating Data in MYSql update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’); For moving wordpress installs to repair the guid UPDATE wp_posts SET guid = replace( guid, ‘web/’, ” ) ;
MySQL Remove whitespace
update your_table set your_field = trim(your_field)
Create joomla 1.5 admin
This is something I do often enough to not want to google it .. Step 1 Create the new Joomla! User REPLACE INTO `jos_users` (`id`, `name`, `username`, `email`, `password`, `usertype`, `block`, `sendEmail`, `gid`, `registerDate`, `lastvisitDate`, `activation`, `params`) VALUES (’60’, ‘Admin2’, ‘admin2’, ‘user@usersemailaddress’, ‘21232f297a57a5a743894a0e4a801fc3:abcdefghijklmnopqrstuvwxyz012345’, ‘Super Administrator’, ‘0’, ‘0’, ’25’, ‘2000-01-01 00:00:00’, ‘0000-00-00 00:00:00’, ”, ”); The
Continue reading: Create joomla 1.5 admin
Adding admin user from phpMyAdmin
here: http://www.dnawebagency.com/how-to-add-an-admin-user-to-the-wordpress-database
Incrementing values in Mysql
As the saga continue manipulating data this comes in handy to increment all the id’s in a table UPDATE jos_content_old SET id = id + 50 WHERE (id = id) Keep in mind the resulting id being set must be higher thatn any existing ones or ther will be an error.
moving data in sql
So after searching around the web looking for code to get data from one table intop another I found this example posted on the mysl dev forum mysql> INSERT INTO orders (customer_cust_id, orderdatetime, message, taxrate, shippingprice) -> SELECT ‘1’, NOW(), null, taxrate, shippingprice FROM customer -> WHERE cust_id=’1′; Query OK, 1 row affected (0.01 sec)
Continue reading: moving data in sql