Search code examples
mysqlsql-updateexcept

How to update all fields but one certain field statement in MySQL?


Suppose I have a table 'tbl' with 4-field schema: f1,f2,f3,f4. I have bunch of data (in csv form whose list order of fields are same as table schema's [cause I design it] but leave the field-f3 value as ',,' in csv) needed to correct from my users but only leave f3 unchanged. I image there's such an update statement as:

UPDATE tbl VALUES ('new-f1-val','new-f2-val', IGNORE, 'new-f3-val')

where when db engine sees this statement, it will recognize IGNORE as a system predefined reserved word/constant that tells db engine to ignore (i.e. NOT TO) updating this particular field. I'm not telling fairy tale cause I constantly face these needs from my users.

Is there such statement in MySQL, whether current or future, or any equivalent statements/procedures to achieve same result?

==================================

Many people don't understand why I image this kind of statement, so I'll tell my practical motivation here:

  1. table 'tbl' might have many fields.
  2. Not to update field 'f3' is permanent condition.
  3. No information about what other fields may be needed to correct, so I'm to update all other fields.
  4. I hope to make update statement short.
  5. I hope there's an update statement pretty much like the insert statement without listing each field name out.
  6. If such statement exists, I can save efforts to modify my "creating-insert-statement' codes to 'updating-update-statement'codes.

==================================

EDIT: I add description of file format of data that users gave me to refine my question.

==================================


Solution

  • You should always explicitly add the columns you want to update in a table, it's good practice, your need for shortening the query notwithstanding.
    That being said, try inserting "null" and checking if it works. No guarantee here, haven't tried so myself.