I have 2 MySQL tables:

1. 'table_a'
2. 'table_b' which contains updated content of 'table_a'.

I want 'table_b' to replace 'table_a' and be called 'table_a', but only if 'table_b' exists. Otherwise, do nothing.

I have tried using a combination of DROP TABLE IF EXISTS table_a and RENAME TABLE table_b TO table_a without any success.

Is this the correct approach?

CodePudding user response：

I think the easiest solution is to:

rename table table_a to table_old, table_b to table_a;

And catch the error. If table_b doesn't exist, neither of the renames is executed.

If its successful, you'll need to remove table_old otherwise it will still be around:

MariaDB [test]> show tables
    -> ;
 ---------------- 
| Tables_in_test |
 ---------------- 
| table_a        |
| table_b        |
 ---------------- 
2 rows in set (0.001 sec)

MariaDB [test]> rename table table_a to table_old, table_b to table_a;
Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> drop table table_old;
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> rename table table_a to table_old, table_b to table_a;
ERROR 1146 (42S02): Table 'test.table_b' doesn't exist

MariaDB [test]> show tables;
 ---------------- 
| Tables_in_test |
 ---------------- 
| table_a        |
 ---------------- 
1 row in set (0.001 sec)