asked on

AUTOMATION OF SQL QUERY IN MYSQL

Hi All,
I would like to automate this process, should run automatically and the result set(s) Can I do this? If yes, then how?
Thanks!

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE '<dbs_name>' AND COLUMN_NAME = '<student_Id>' AND DATA_TYPE= 'varchar'

CREATE TABLE student_id_new LIKE student_id;

ALTER TABLE student_id RENAME student_id_old;

INSERT INTO student_id_new SELECT * FROM student_id_old;

ALTER TABLE student_id_new RENAME student_id;

DROP TABLE student_id_old;

arnold

You could do this using cron and a shell script. But you inevitably will run into issues if there are constraints/indexes/foreign keys in other tables relying on this one.
What is the point for doing this?

Mark Bullock

To run mysql scripts from the command line:

mysql db_name < script.sql > output.tab

Open in new window

Complete info is here: http://dev.mysql.com/doc/refman/5.6/en/mysql.html

Roteberg

ASKER

Thank you Arnold for your answer the purpose of this is automate those script instead of running it mannually. @ Mark thank you as well I will check that out

arnold

I understand that is to automate, the only reason I think to run this is because your ID column/s is set too low such that every so often after entries are deleted, you need to reset.
You are copying data from the old table into the new without changes.
Why not run the alter table and change the column that causes you problems updating other columns in tables that reference thus one.

Roteberg

ASKER

Thanks Arnold, I want to convert all student_id that are in var to int on all affected tables, so you mean I can just ALTER the table and change the column on a production server without going through those process?

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial