Sanju Menon Sanju Menon - 6 months ago 18
SQL Question

MySQl query to UPDATE if Data is Null or Empty with a CASE Statement

Iam struck up with a query. When i edit a data in Table A, it needs to check if the same data is present in Table B, If data is present in Table B, just ignore updation, if data is empty or Null, then the data from Table A needs to Update in Table B. With the following query iam almost achieving it but the issue is When the Data is present in Table B, its just deleting the data. It should actually ignore it when data is present. A small issue with the case statement i guess. Pls help me on this.

$strSQLInsert2 = "UPDATE Table B
SET
`tender_intendername` = CASE WHEN `tender_intendername`='' or `tender_intendername` IS NULL
THEN '".$values["intendername1"]."' END,
`no_of_participants` = CASE WHEN `no_of_participants`='' Or `no_of_participants` IS NULL
THEN '".$values["no_of_participants"]."' END
WHERE tender_id=" . $values["tender_id"];

Answer

You have to add an ELSE to the CASE expression:

UPDATE Table B
SET `tender_intendername` = CASE 
                               WHEN `tender_intendername`='' or 
                                    `tender_intendername` IS NULL
                                  THEN '".$values["intendername1"]."' 
                               ELSE `tender_intendername` END,
    `no_of_participants` = CASE 
                              WHEN `no_of_participants`='' Or 
                                   `no_of_participants` IS NULL 
                                 THEN '".$values["no_of_participants"]."' 
                              ELSE  `no_of_participants` END
WHERE tender_id=" . $values["tender_id"];

The ELSE statement sets each field equal to itself, hence it guarantees that the field will not change when the field is not empty and not NULL.

Comments