1

I want to run a few TRUNCATE statements. But in between some of the database doesn't exists. But SQL stops the rest of the queries.

Is there any way to ignore the error and continue to execute the rest of the statements.

TRUNCATE _aaaaa_tmp;
TRUNCATE _bbbbb_tmp;
TRUNCATE _ccccc_tmp;

Error Code: 1146. Table '_bbbbb_tmp' doesn't exist
  • IF OBJECT_ID('schema.table') IS NOT NULL do the job – McNets May 22 at 15:52
  • @McNets - I think OBJECT_ID is unique to SQL Server, and not available in MySQL. – Rick James May 22 at 16:35
0

It depends on how you are executing those queries:

  • If you are loading an SQL file, execute then with --force, and errors will be ignored (all errors! So if you want to catch other possible issues, that is going to be a problem)
  • If run from an application, you can execute each command individually and only ignore on truncate for the specific table not found error
  • With some sql logic, you can check for table existence on information_schema.tables, although you may need to create a procedure just for that (raw command line doesn't allow for programming logic directly).

I think implementing that outside of SQL would be the preferred method (there is no "good" dynamic SQL execution on MySQL (other than tricks with prepared statements). E.g. a bash one liner (no error or escaping handling):

mysql -BN -e "SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
              FROM information_schema.tables 
              WHERE table_schema in ('schemas', 'to', 'process') 
              AND table_name like '\_%\_tmp'" 
| while read table; do mysql -e "TRUNCATE TABLE $table"; done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.