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

  • Take a look here – Noam Kremen Sep 20 '11 at 15:05
  • The public interface to a database includes column names; it doesn't include column data types. How is it that you know the data type, but not the column name? – Mike Sherrill 'Cat Recall' Sep 20 '11 at 22:38
  • This is actually a very good question because developers who created tools to perform SQL needed to fetch and update table could relate well to this question and @marcioAlmada's answer. +1 !!! – RolandoMySQLDBA Sep 21 '11 at 15:35

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
    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.

| improve this answer | |
  • I like this answer because you have to make information_schema spit out the SQL. I do this when I collect the grants against the mysql.user table. I do SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''; and then pipe back to mysql and it spits out the full SQL grants to create all non-anonymous users. You have to do the same with the information_schema as your answer clearly says. Yours is a +1 !!! – RolandoMySQLDBA Sep 21 '11 at 15:21
  • I also added an answer I submitted four months back that corroborates your proposed answer. – RolandoMySQLDBA Sep 21 '11 at 15:36

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.