Mysql – How to i update the database from another database using temporary tables

MySQLoptimizationperformancetemporary-tables

I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it?

This my temporary table : 

use data_pro; 
DROP TABLE IF EXISTS datasll.table1;
CREATE TEMPORARY TABLE IF NOT EXISTS datasll.table1 
SELECT 
 cs.id,
 cs.fk_c,
 cs.so, 
 cs.pr,
 cs.date,
 cs.o_p,
 cs.sh_c,
 cs.x,
 LEFT(cs.x,2),
 cs.doc,
 cs.fk_op,
 cc.id_c_c,
 cc.name,
 cc.descrip,
 left(cc.n_o,3) as bon,
 cc.fk_c_br,
 cc.de,
 cc.m_mat,
 cc.color, 
 cc.fk_c_s
 FROM
   data_pro.catas cs

 LEFT OUTER JOIN
data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
WHERE
data_pro.cc.pap = 1 AND   data_pro.cc.stati = 'b';

Many thanks for any help.

Best Answer

You can indeed use stored procedures to refresh the data in your database. It just means coding the INSERTs and UPDATEs appropriate to your data.

If you are replacing the existing data with new data, you could use:

INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty table. Of course, you could run individual steps to update many tables, one at a time.

Syntax documentation on INSERT INTO ... SELECT FROM is here: https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

If you want to UPDATE ... SELECT FROM for only some values in a table, you could follow the different examples here:

https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

Also general UPDATE syntax is described here: https://dev.mysql.com/doc/refman/5.7/en/update.html

Related Question