title: how do i make cases in mysql to update fields only if empty, if not empty, go to next field?
body: <p>i'm searching around but can't decipher the scenarios people are trying to do and adapt to my scenario.</p>
  
  <p>there is one <a href="https://stackoverflow.com/questions/11176410/update-mysql-field-if-field-is-not-empty-go-to-next-one">stackoverflow q&amp;a</a> that is close to my question, but the answer is weird and i don't know if the dashes in the answer are supposed to be there.  plus i'm not sure how the names before and after the questionmark work and what names in my scenario should go where, either before or after the question marks.</p>
  
  <p>so i need to update 2 fields when submitting a form.  if the 2 fields have data, don't use those 2 fields, use another 2 fields, but if those other 2 fields are filled, then use a 3rd set of 2 fields, and so forth.</p>
  
  <p>i know this is a bad technique, and i should do normalization, but this is just a simple app where at the most 2 people might be trying to submit said form at the same time, and the chances of that happening are 100,000 to 1, but still i want to prevent it on the off chance it might happen.</p>
  
  <p>my table is like this:</p>
  
  <p>table name is jobs and these are the fields</p>
  
  <pre><code> id
   name
   path
   jobid
   note1
   note1user
   note2
   note2user
   note3
   note3user
   note4
   note4user
   note5
   note5user
   note6
   note6user
  </code></pre>
  
  <p>so if note1 and note1user is not null or empty, use note2 and note2user.
  if note2 and note2user is not null or empty, use note3 and note3user.
  and so forth.
  if note6 and note6user has data, it doesn't matter because the form doesn't show up for submitting.</p>
  
  <p>the code i saw somewhere else goes like this, but wasn't sure how to adapt it to my scenario:</p>
  
  <pre><code>update table_name set  
       col1 = ( case when col1 is null then ? else col1 end )  
      , col2 = ( case when col2 is null then ? else col2 end )  
  --  , col3 = ...
  ;
  </code></pre>
  
  <p>i'll try and guess something.  how does the following chunk of code look?  i took off those dashes, cause i didn't like how it looked in there.  not even sure the semicolon is needed.  and a final thought is that this is for a one field at a time scenario.  how do i do 2 fields at a time?  in other words, how do i incorporate note1user through note6user?</p>
  
  <pre><code>update jobs set  
      note1 = ( case when note1 is null then ? else note1 end ),
      note2 = ( case when note2 is null then ? else note2 end ),
      note3 = ( case when note3 is null then ? else note3 end ),
      note4 = ( case when note4 is null then ? else note4 end ),
      note5 = ( case when note5 is null then ? else note5 end )
  </code></pre>
  
  <p>thanks if you provide any tips and tricks and snippets.</p>
  
  <p>UPDATE</p>
  
  <p>this is to Barmar.</p>
  
  <p>this doesn't create errors, but nothing gets written to database.  don't worry that it says update files.  that's the table i'm trying to write to.  i messed up my question when i said it was jobs.  also, the following does the same whether i put all the notes together and note users together, or i put one of each going down.</p>
  
  <pre><code>UPDATE files SET 
  note_6 = (CASE WHEN note_6 IS NULL AND note_5 IS NOT NULL THEN '$note_6' ELSE note_6 END), 
  note_6_user = (CASE WHEN note_6_user IS NULL AND note_5_user IS NOT NULL THEN '$note_6_user' ELSE note_6_user END), 
  note_5 = (CASE WHEN note_5 IS NULL AND note_4 IS NOT NULL THEN '$note_5' ELSE note_5 END), 
  note_5_user = (CASE WHEN note_5_user IS NULL AND note_4_user IS NOT NULL THEN '$note_5_user' ELSE note_5_user END), 
  note_4 = (CASE WHEN note_4 IS NULL AND note_3 IS NOT NULL THEN '$note_4' ELSE note_4 END), 
  note_4_user = (CASE WHEN note_4_user IS NULL AND note_3_user IS NOT NULL THEN '$note_4_user' ELSE note_4_user END), 
  note_3 = (CASE WHEN note_3 IS NULL AND note_2 IS NOT NULL THEN '$note_3' ELSE note_3 END), 
  note_3_user = (CASE WHEN note_3_user IS NULL AND note_2_user IS NOT NULL THEN '$note_3_user' ELSE note_3_user END), 
  note_2 = (CASE WHEN note_2 IS NULL AND note_1 IS NOT NULL THEN '$note_2' ELSE note_2 END), 
  note_2_user = (CASE WHEN note_2_user IS NULL AND note_1_user IS NOT NULL THEN '$note_2_user' ELSE note_2_user END), 
  note_1 = (CASE WHEN note_1 IS NULL THEN '$note_1' ELSE note_1 END), 
  note_1_user = (CASE WHEN note_1_user IS NULL THEN '$note_1_user' ELSE note_1_user END) 
  WHERE id='$id'
  </code></pre>
  
  <p>UPDATE</p>
  
  <p>the final thing that allowed it to work is making the fields in the database have a default of NULL.  i couldn't get it to work with empty.</p>