TIBCO LogLogic LMI - Create table using a select query inside MySQL
Article ID: KB0077069
Updated On:
| Products | Versions |
|---|---|
| TIBCO LogLogic Enterprise Virtual Appliance | all versions |
Description
When making changes to system tables inside LMI, it is worthwhile making a backup. This can be done via mysqldump, but it may be that you wish to keep the data in tabular form, e.g. when modifying aggregation or table rules. To do so, use the following syntax:
Issue/Introduction
This article shows how to create a MySQL table via a select query.
Resolution
MySQL supports what is known as a sub-select, or a select clause that is inside of a larger query. In this case a select query is being used inside of a larger create query. The results of the select query are directly used as input to the create query which means the new table is automatically populated with data from the table specified in the select query.
create table <NEW_TABLE_NAME> select [query details]
So, for example:
mysql> create table cfgTableRule_bak select * from cfgTableRule;
Query OK, 238 rows affected (0.01 sec)
Records: 238 Duplicates: 0 Warnings: 0
In this query all rows of data from cfgTableRule are returned and used to populate the new cfgTableRule_bak table. If the cfgTableRule needs to be restored it can simply be dropped and the cfgTableRule_bak table renamed (using the alter query).
create table <NEW_TABLE_NAME> select [query details]
So, for example:
mysql> create table cfgTableRule_bak select * from cfgTableRule;
Query OK, 238 rows affected (0.01 sec)
Records: 238 Duplicates: 0 Warnings: 0
In this query all rows of data from cfgTableRule are returned and used to populate the new cfgTableRule_bak table. If the cfgTableRule needs to be restored it can simply be dropped and the cfgTableRule_bak table renamed (using the alter query).
Feedback
Yes
No
Powered by
