CODEWITHSUNDEEP
mysql
mojobullfrog
mojobullfrog

Reputation: 189

Replacing a table if another table exists

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?

Upvotes: 0

Views: 77

Answers (1)

danblack
danblack

Reputation: 14691

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)

Upvotes: 3

Related Questions