RemiC RemiC - 4 months ago 19
SQL Question

conditional insert into mysql



I am quite newbie with MySQL (5.1) and I don't achieve to do a conditional "insert into".

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.

Here my query:


INSERT INTO DB.table_destination(field1, field2)
SELECT
CASE
WHEN fieldAA='value1' THEN (
SELECT fieldA, fieldB
FROM DB.table_source<BR>
)
ELSE (
SELECT fieldC, fieldD
FROM DB.table_source
)
END
FROM db.table_source




Note that the fieldAA belongs to the table_source too.

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.

I am stuck on that issue for a while, so I will greatly appreciate your help.



[EDIT]


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.


Here my query that returns me the result from only one condition:

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)




Tables details

1) odk.test_insee contains SHAPE (geometry used in the spatial function GISWithin) and insee_zerofill.

2) DEMO_OISEAUX9_FULL_CORE contains GPS_TEL_LNG & GPS_TEL_LAT (filled when METHODE_LOC is equal to 13), ID_SITE (filled when METHODE_LOC is different from 13).

3) odk.site_clone contains INSEE_zero, LONG_LIEU_DIT, LAT_LIEU_DIT.



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!


[EDIT 2]

Now, it works! I should have repeated the specific condition within the CASE for EACH field to select, as followed:


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 ;

Answer

this wawy for case

INSERT INTO DB.table_destination(field1, field2) 
SELECT CASE fieldAA   WHEN 'value1' THEN fieldA  ELSE fieldC END ,
       CASE fieldAA   WHEN 'value1'  THEN fieldB  ELSE fieldD END 
FROM db.table_source 

If i understand correctly you question and you want perform your insert select only if ID_SITE is null then you should use

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)
AND ID_SITE is null ;

you can use both case when is null

INSERT INTO DB.table_destination(field1, field2) 
SELECT CASE fieldAA   WHEN is null THEN fieldA  ELSE fieldC END ,
       CASE fieldAA   WHEN 'value1'  THEN fieldB  ELSE fieldD END 
FROM db.table_source 

or ifnull

INSERT INTO DB.table_destination(field1, field2) 
SELECT ifnull( fieldAA, your_column_for_null) ,
       CASE fieldAA   WHEN 'value1'  THEN fieldB  ELSE fieldD END 
FROM db.table_source 
Comments