Is it possible to fetch or update table data by table field type?

Question

Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.

Answer 1

Yes, it's very possible to accomplish that but not with a unique query!

First, you need to know where to look for metadata: This kind of information is stored into an internal mysql database called information_schema, this database in used only internally by Mysql every time you execute any kind of query involving any database entities. There you will find all sort of meta information about all the tables, columns, privileges, users, triggers of your databases:

show tables from information_schema;

The solution I know for your problem involves two queries. First one will return a list of columns with a given data_type (you can specify the database, and table you want to look for too):

select columns.column_name from
    information_schema.schemata
    ,information_schema.tables
    ,information_schema.columns
where 
    tables.table_schema = schemata.schema_name
    and tables.table_name =  columns.table_name
    and schemata.schema_name = 'your_database_name'
    and tables.table_name = 'your_table_name'   
    and columns.data_type = 'the_field's_data_type_you_want_to_filter'
    -- + any other filter you find useful

With the list of column names that satisfy the properties you want (a given data type, in your specific case) all you need to do is a secondary query to select or update data from that list of fields.

I searched in MySql documentation and unfortunately there is no way to select or update a dynamic list of fields only with SQL, thats why a secondary generated query is needed.

Hope this helps.