Pupulate table with data in mysql

 

I have two databases

  • db1

  • db2

    • db1 has table controller below the description of controller table. 

    +-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| id                    | int(11)      | NO   | PRI | NULL    |       |
| interface_name        | varchar(255) | YES  |     | NULL    |       |
| store_number          | int(11)      | YES  |     | NULL    |       |
| file_seq_no           | int(11)      | YES  |     | NULL    |       |
| packet_seq_no         | int(11)      | YES  |     | NULL    |       |
| last_trans_start_date | date         | YES  |     | NULL    |       |
| last_trans_end_date   | date         | YES  |     | NULL    |       |
| last_extract_datetime | datetime     | YES  |     | NULL    |       |
| is_enabled            | char(1)      | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+

    and same table description and additional column(date_opened) with different name available in database db2 with name store table.

    My job is to write a query based on below logic.

    Point 1 : Fetch all store_number, date_opened from db2.store.

    Point 2 : Fetch all store_number from db1.controller table into list.

    Point 3 : Filter new store_number fetched from db2.store not present in db1.controller.

    For each of these new store_number

    insert default initial values for new stores, file_seq_number and packet_seq_number will be set as zero.

    last_transaction_start_date , last_transaction_end_date , last_extract_timestampwill be store creation date ie db2.store.date_opened

    enabled will be set as 1

    Main query would be 

    Insert into db1.controller table values( store_number, 0, 0,$date_opened,$date_opened,
$date_opened,1);

    Can any one please help me to complete this sql query.

    Thanks in advance

    You can get filtered data by two ways.

    With the help of subquery 

    Select db2.store .* from db2.store  where db2.store.id not in(Select db1.controller.id from db1.controller)

    Here all new data which is not present in db1.controller will be displayed. You can display same result With the help of join also. 

     SELECT db2.store.* FROM  db2.store  LEFT JOIN db1.controller ON db2.store.ID = db1.controller.ID WHERE db1.controller.ID IS NULL

    You will get the data which is not present in db1.controller now iterate the data and execute your insert query.

    Update

    You can try below single query to add filtered records. 

    Insert into db1.controller (id,interface_name,store_number,file_seq_no,packet_seq_no,last_trans_start_date,last_trans_end_date,last_extract_datetime,is_enabled)  SELECT db2.store.id, db2.store.interface_name, db2.store.store_number, 0, 0, db2.store.date_opened, db2.store.date_opened, db2.store.date_opened, db2.store.is_enabled  FROM  db2.store  LEFT JOIN db1.controller ON db2.store.ID = db1.controller.ID WHERE db1.controller.ID IS NULL;

    This something what i did

    INSERT INTO db1.controller (interface_name,store_number,file_seq_no,packet_seq_no,last_trans_start_date,last_trans_end_date,last_extract_datetime,is_enabled) SELECT 'test',s.store_number,0,0,s.date_opened,s.date_opened,s.date_opened,'1' FROM db2 . store s where s.store_number not in (select store_number from db1.controller);

    链接地址: http://www.djcxy.com/p/94228.html

    上一篇: 根据不同表格中的值自动更新表格

    下一篇: 使用mysql中的数据来整理表格