MySQL - Update field using 'case when then select' with no existing tables

Question

Supposing a MySQL 5.7 database with a schema named myschema, where exists only one table of these three: table_a, table_b, or table_c. If there is only one table, for example table_a, the following query throws a Error Code: 1146. Table 'myschema.table_b' doesn't exist.

UPDATE myschema.table_d d 
SET d.d_value = 
(CASE
    WHEN EXISTS (SELECT * 
                   FROM INFORMATION_SCHEMA.TABLES 
                  WHERE TABLE_SCHEMA = 'myschema' 
                    AND TABLE_NAME = 'table_a')
           THEN (SELECT MAX(a.a_value) 
                   FROM myschema.table_a a 
                  WHERE a.table_d_id = d.id)
    WHEN EXISTS (SELECT * 
                   FROM INFORMATION_SCHEMA.TABLES 
                  WHERE TABLE_SCHEMA = 'myschema' 
                    AND TABLE_NAME = 'table_b') 
           THEN (SELECT MAX(b.b_value) 
                   FROM myschema.table_b b 
                  WHERE b.table_d_id = d.id)
    WHEN EXISTS (SELECT * 
                   FROM INFORMATION_SCHEMA.TABLES 
                  WHERE TABLE_SCHEMA = 'myschema' 
                    AND TABLE_NAME = 'table_c')
           THEN (SELECT MAX(c.c_value) 
                   FROM myschema.table_c c 
                  WHERE c.table_d_id = d.id)
    ELSE 0
END);

So I suppose it's necessary that all tables must exist to perform the case when [boolean expression] then select [value] from [table] statements. Am I sure? If so, is there another way to do this? Thanks in advance.

score 0 · Accepted Answer · edited May 23 '17 at 12:15

You could use statements to do that:

PREPARE stmt FROM 
'SELECT concat(''SELECT MAX('', replace(table_name, ''table_'', '' ''), ''_VALUE) FROM '', TABLE_NAME, '' INTO @b;'') INTO @a
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME IN (''table_a'', ''table_b'', ''table_c'')
AND TABLE_SCHEMA = ''myschema''';
EXECUTE stmt;

PREPARE stmt2 FROM @a;

UPDATE myschema.table_d d SET d.d_value = @b;

Note: I answered it with some help from the answers from (How To have Dynamic SQL in MySQL Stored Procedure)

MySQL - Update field using 'case when then select' with no existing tables

1 Answers1