title: conditional insert into mysql
body: <p><BR>
  I am quite newbie with MySQL (5.1) and I don't achieve to do a conditional "insert into".<BR>
  For each record, I want to insert into the same columns (1 and 2) from an empty/destination table, either 2 fields (A and B) or 2 other fields (C and D) from a source table containing several records.<BR>
  Here my query:<BR></p>
  
  <pre><code>INSERT INTO DB.table_destination(field1, field2)
  SELECT
      CASE 
          WHEN fieldAA='value1' THEN (
              SELECT fieldA, fieldB   
              FROM DB.table_source&lt;BR&gt;
              )
          ELSE (
              SELECT fieldC, fieldD
              FROM DB.table_source
              )
      END
  FROM db.table_source
  </code></pre>
  
  <p><BR>
  Note that the fieldAA belongs to the table_source too.<BR>
  I have an error around the second SELECT. Depending on some tiny changes in my script, it sometimes complains that I have more than one row.<BR>
  I am stuck on that issue for a while, so I will greatly appreciate your help.
  <BR><BR>
  [EDIT]
  <BR>
  Thanks a lot for your advices! It works perfectly with this simple example.
  However, my study case is in the real life a bit more complex, as I have to deal with 3 source tables, including one condition (JOIN) between tables_source_1 and table_source_2, and one spatial function between table_source_1 and table_source_3.<BR></p>
  
  <p>Here my query that returns me the result from only one condition:</p>
  
  <pre><code>INSERT INTO test.OBSERVATION (CODE_INSEE,ID_SITE,LONGWGS84,LATWGS84)
      SELECT
          CASE METHODE_LOC WHEN '13' THEN insee_zerofill ELSE INSEE_zero END,
          CASE METHODE_LOC WHEN '13' THEN '' ELSE ID_SITE END,
          CASE METHODE_LOC WHEN '13' THEN GPS_TEL_LNG ELSE LONG_LIEU_DIT END,
          CASE METHODE_LOC WHEN '13' THEN GPS_TEL_LAT ELSE LAT_LIEU_DIT END
      FROM odk.test_insee, odk.site_clone RIGHT JOIN odk.DEMO_OISEAUX9_FULL_CORE ON odk.site_clone.ID_SITE=odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS
      WHERE odk.GISWithin(GeomFromText(CONCAT('POINT(',GPS_TEL_LNG,' ',GPS_TEL_LAT,')')), SHAPE)
  </code></pre>
  
  <p><BR>
  Tables details<BR>
  1) odk.test_insee contains SHAPE (geometry used in the spatial function GISWithin) and insee_zerofill.<BR>
  2)    DEMO_OISEAUX9_FULL_CORE contains GPS_TEL_LNG &amp; GPS_TEL_LAT (filled when METHODE_LOC is equal to 13), ID_SITE (filled when METHODE_LOC is different from 13).<BR>
  3) odk.site_clone contains INSEE_zero, LONG_LIEU_DIT, LAT_LIEU_DIT.
  <BR><BR>
  I tried to set a CASE in the WHERE condition to tell it to perform the function only when ID_SITE is null, but without success so far.
  Any ideas?
  Thanks in advance!<br></p>
  
  <p>[EDIT 2]<br>
  Now, it works! I should have repeated the specific condition within the CASE for EACH field to select, as followed:<br> </p>
  
  <pre><code>SELECT
      CASE METHODE_LOC WHEN '13' THEN 
          (SELECT insee_zerofill
          FROM odk.test_insee, odk.DEMO_OISEAUX9_FULL_CORE
          WHERE odk.GISWithin(GeomFromText(CONCAT('POINT(',`GPS_TEL_LNG`,' ',`GPS_TEL_LAT`,')')), SHAPE)  )
      ELSE 
          (SELECT INSEE_zero
          FROM odk.site_clone RIGHT JOIN odk.DEMO_OISEAUX9_FULL_CORE 
          ON odk.site_clone.ID_SITE=odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS
          WHERE odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS is not NULL  )
      END,
      CASE METHODE_LOC WHEN '13' THEN ''
      ELSE 
          (SELECT ID_SITE
          FROM odk.site_clone RIGHT JOIN odk.DEMO_OISEAUX9_FULL_CORE 
          ON odk.site_clone.ID_SITE=odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS
          WHERE odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS is not NULL  )
      END,
      CASE METHODE_LOC WHEN '13' THEN 
          (SELECT GPS_TEL_LNG
          FROM odk.test_insee, odk.DEMO_OISEAUX9_FULL_CORE
          WHERE odk.GISWithin(GeomFromText(CONCAT('POINT(',`GPS_TEL_LNG`,' ',`GPS_TEL_LAT`,')')), SHAPE)  )
      ELSE 
          (SELECT LONG_LIEU_DIT
          FROM odk.site_clone RIGHT JOIN odk.DEMO_OISEAUX9_FULL_CORE 
          ON odk.site_clone.ID_SITE=odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS
          WHERE odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS is not NULL  )
      END,
      CASE METHODE_LOC WHEN '13' THEN 
          (SELECT GPS_TEL_LAT
          FROM odk.test_insee, odk.DEMO_OISEAUX9_FULL_CORE
          WHERE odk.GISWithin(GeomFromText(CONCAT('POINT(',`GPS_TEL_LNG`,' ',`GPS_TEL_LAT`,')')), SHAPE)  )
      ELSE 
          (SELECT LAT_LIEU_DIT
          FROM odk.site_clone RIGHT JOIN odk.DEMO_OISEAUX9_FULL_CORE 
          ON odk.site_clone.ID_SITE=odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS
          WHERE odk.DEMO_OISEAUX9_FULL_CORE.SITE_OBS is not NULL  )
      END
  FROM odk.DEMO_OISEAUX9_FULL_CORE ;  
  </code></pre>