0

I need to update the default value of all Synced fields in a database tables.

I've looked at several other posts and put together the following, which I'm missing something, any ideas?

ALTER TABLE (
  SELECT 
    DISTINCT
      TABLE_NAME
  FROM
    INFORMATION_SCHEMA.COLUMNS
  WHERE 
    TABLE_SCHEMA != 'mysql'
    AND TABLE_SCHEMA != 'information_schema'
    AND COLUMN_NAME = 'Synced'
  ORDER BY 
    TABLE_NAME ASC)
CHANGE `Synced` `Synced` TINYINT DEFAULT 0;

I've referenced the following to put it together:

halfer
  • 19,824
  • 17
  • 99
  • 186
James Oravec
  • 19,579
  • 27
  • 94
  • 160
  • You cannot use a subquery for a table definition. To do what you want, you need to generate SQL in a string variable and then use `prepare` and `execute` to run the statement. – Gordon Linoff May 06 '14 at 15:46

1 Answers1

1

I think you want some dynamic SQL. Like this:

SELECT 
  DISTINCT
    CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `Synced` `Synced` TINYINT DEFAULT 0;') 
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA != 'mysql'
    AND TABLE_SCHEMA != 'information_schema'
    AND COLUMN_NAME = 'Synced'
ORDER BY 
    TABLE_NAME ASC
James Oravec
  • 19,579
  • 27
  • 94
  • 160
dwjv
  • 1,227
  • 9
  • 15
  • I updated the code to include `DISTINCT` in the query. Is there a way to execute each of these dynamic queries after building them, in sql, not in code. – James Oravec May 06 '14 at 16:17