0
DROP PROCEDURE IF EXISTS update_migration;

DELIMITER //

CREATE PROCEDURE update_migration ()
BEGIN    

IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = `bid_log` AND COLUMN_NAME = `Paid` ) THEN
    ALTER TABLE `bid_log`  ADD `Paid` INT NULL;
END IF; 

END //

DELIMITER ;

I'm using SELECT 1 FROM the schema table to check if a column exists, but if the column doesn't exist, I get this error:

ERROR 1054 (42S22) at line 1: Unknown column 'bid_log' in 'where clause'

How can I check for an existing column without mysql throwing an error for having a missing value in my select query?

Jay Shri
  • 119
  • 3
  • 12
  • I'm voting to close this as off-topic, caused by a simple typographical error, because you used the wrong quotes around `bid_log` and hence told the parser that it's the name of an object, not a string constant. – underscore_d Apr 06 '18 at 14:36
  • @underscore_d So then are you saying there is no way to escape a table name used inside an `ALTER` statement, which is inside an `IF` block? – Tim Biegeleisen Apr 06 '18 at 14:38
  • 1
    https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql I should be using back ticks to select tables and columns. Using single quotes throws a syntax error. – Jay Shri Apr 06 '18 at 14:38
  • 1
    No, I'm saying that if you want to compare something to the value of `table_name`, then that thing needs to have the same type; you cannot compare a column like `table_name`, which is a string, to an object identifier, which simply does not go there, syntactically. Your link proves nothing, except that you're just trying to mash random syntax together and expecting a working result, which isn't going to work. – underscore_d Apr 06 '18 at 14:39
  • @underscore_d Good catch, I missed that. If using single quotes fixes it, then this is indeed a typo question. – Tim Biegeleisen Apr 06 '18 at 14:41
  • @RaymondNijland This is inside a stored procedure. I will update my question now with the full code. – Jay Shri Apr 06 '18 at 14:41
  • 1
    the complete SQL should have be there from the start. – Raymond Nijland Apr 06 '18 at 14:44
  • @underscore_d So you're saying that I should try `SELECT 1 FROM information_schema.columns WHERE table_name = 'bid_log' AND COLUMN_NAME = 'Paid'` I'm assuming column_name is string too – Jay Shri Apr 06 '18 at 14:45
  • 1
    It would seem quicker to try that, rather than to ask me whether that's what I'm suggesting you try! But yes, that is what I meant. – underscore_d Apr 06 '18 at 14:49

1 Answers1

0
SELECT 1 FROM information_schema.columns WHERE table_name = 'bid_log' AND COLUMN_NAME = 'Paid'

In the schema table, table_name and col_name are stored as string values, so they must be compared to string identifiers instead of object identifiers.

Thanks to underscore_d for the answer

Jay Shri
  • 119
  • 3
  • 12