Update Mysql(if 2 columns is null go to next 2 columns)

Question

https://stackoverflow.com/a/11177086/1947965

from the correct answer link above I want to follow up another question. because all I want to do is update set of fields when the this set of fields are empty else update to another set of fields not just only a single column. Here is another situation:

1.) If subjectColumnA, timeColumnA, dataColumnA is NULL update fields.

2.) If subjectColumnA, timeColumnA, dataColumnA is Not NULL AND subjectColumnB, timeColumnB, dataColumnB is NULL update fields subjectColumnB, timeColumnB, dataColumnB

and so on. . .

From @ravinder's query I want to ask having this scenario: Let us say this is the content of the table

+------+------+------+------+------+------+------+------+------+------+
| id   | AAA1 | BBB1 | CCC1 | AAA2 | BBB2 | CCC2 | AAA3 | BBB3 | CCC3 |
+------+------+------+------+------+------+------+------+------+------+
|    1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+

From the condition above I mention 'AAA1, BBB1, and CCC1' is NULL so this columns will be updated. And the result will be like this.

+------+------+------+------+------+------+------+------+------+------+
| id   | AAA1 | BBB1 | CCC1 | AAA2 | BBB2 | CCC2 | AAA3 | BBB3 | CCC3 |
+------+------+------+------+------+------+------+------+------+------+
|    1 |    2 |    2 |    2 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+

Another instances is that when 'AAA1, BBB1, CCC1' is 'NOT NULL' then 'AAA2, BBB2, and CCC2' will be updated which the result will be like this.

+------+------+------+------+------+------+------+------+------+------+
| id   | AAA1 | BBB1 | CCC1 | AAA2 | BBB2 | CCC2 | AAA3 | BBB3 | CCC3 |
+------+------+------+------+------+------+------+------+------+------+
|    1 |    3 |    3 |    3 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+

Is it possible?? Since what I have read from others questions and the answers Using CASE and If uses 2 arguments only. I tried this querying like this one but it fails.

UPDATE table SET
    AAA3 = IF( AAA2 IS NOT NULL, 2, AAA3 ) AND BBB3 = IF( BBB2 IS NOT NULL, 2, BBB3 ),
    AAA2 = IF( AAA1 IS NOT NULL, 2, AAA2 ) AND BBB2 = IF( BBB1 IS NOT NULL, 2, BBB2 ),
    AAA1 = IF( AAA1 IS NULL, 1, AAA1 ) AND BBB1 = IF( AAA1 IS NULL, 1, BBB1 );

What should be the result if subjectColumnA and timeColumnA are null and dataColumnA is not null? — Chetter Hummin, Jan 29 '13 at 04:36

sgeddes · Accepted Answer · 2013-01-29T14:07:44.443

Really hard to answer this without having sample data and desired output, but using the link you supplied and your pseudo syntax above, you should be able to do it the exact same way:

UPDATE TableName SET 
subjectColumnA = ( case when subjectColumnA is null 
                        AND timeColumnA is null 
                        AND dataColumnA is null 
                    then ? 
                    else subjectColumnA end ),
timeColumnA = ( case when subjectColumnA is null 
                        AND timeColumnA is null AND 
                        dataColumnA is null 
                     then ? 
                     else timeColumnA end ),
dataColumnA = ( case when subjectColumnA is null AND 
                        timeColumnA is null AND 
                        dataColumnA is null 
                     then ? 
                     else dataColumnA end ),
subjectColumnB = ( case when subjectColumnA is not null AND 
                        timeColumnA is not null AND 
                        dataColumnA is not null AND 
                        subjectColumnB is null AND 
                        timeColumnB is null AND 
                        dataColumnB is null 
                    then ? 
                    else subjectColumnB end ),
timeColumnB = ( case when subjectColumnA is not null AND 
                        timeColumnA is not null AND 
                        dataColumnA is not null AND 
                        subjectColumnB is null AND 
                        timeColumnB is null AND 
                        dataColumnB is null 
                    then ? 
                    else timeColumnB end ),
dataColumnB = ( case when subjectColumnA is not null AND 
                        timeColumnA is not null AND 
                        dataColumnA is not null AND 
                        subjectColumnB is null AND 
                        timeColumnB is null AND dataColumnB is null 
                    then ?
                    else dataColumnB end )

Really, just add your criteria and if it's not met, in your else, update back to itself.

--EDIT

Using your query above, you can still use CASE -- works the same was as IIF:

UPDATE table SET
    AAA3 = CASE WHEN AAA2 IS NOT NULL THEN 2 ELSE AAA3 END,
    BBB3 = CASE WHEN BBB2 IS NOT NULL THEN 2 ELSE BBB3 END,
    AAA2 = CASE WHEN AAA1 IS NOT NULL THEN 2 ELSE AAA2 END,
    BBB2 = CASE WHEN BBB1 IS NOT NULL THEN 2 ELSE BBB2 END,
    ...

Good luck.

Thanks for the quick reply. I ask this if I could update 3 column fields at once using when criteria has met. Sorry for the post i'll edit it to be more specific. — Minchee Wongsaii Kho, Jan 29 '13 at 13:15
@sgeddes : I like your solution its great but it seems that the codes are long hence I want to make it more shorter if possible. — Minchee Wongsaii Kho, Jan 29 '13 at 13:44
I've edited my answer -- please let me know if I'm misunderstanding your where criteria. I just took your query above and used CASE -- it works the same was as IIF -- and you can string CASE's together if you need nested IFs. Good luck. — sgeddes, Jan 29 '13 at 14:08
thanks it works now with little modification in it. :) I used your query then just did add stuff in my php. — Minchee Wongsaii Kho, Jan 30 '13 at 01:39

Update Mysql(if 2 columns is null go to next 2 columns)

1 Answers1