yunji
yunji

Reputation: 141

Something strange with IF in SQL

I hava a update sql like below:

update saletargets_import_data as st_im left join saletargets as st
on st_im.saletarget_hospital_id=st.saletarget_hospital_id
and st_im.saletarget_product_id=st.saletarget_product_id
set st_im.record_id=if(st.deleted=0 and st.id is not null,st.id,st_im.record_id)
where st_im.import_id=383;

it does not affect.which return: 0 row(s) affected Rows matched: 1 Changed: 0 Warnings: 0 and the st_im.record_id dont update

however, when i use like below:

update saletargets_import_data as st_im left join saletargets as st
on st_im.saletarget_hospital_id=st.saletarget_hospital_id
and st_im.saletarget_product_id=st.saletarget_product_id
set st_im.record_id=if(st.id is not null,st.id,st_im.record_id)
where st_im.import_id=383 and st.deleted=0;

it works, the st_im.record_id update!, but I do not know why… Clould anyone can figure the difference between these two SQL?

Upvotes: 1

Views: 38

Answers (1)

sagi
sagi

Reputation: 40481

Try this one:

update saletargets_import_data as st_im
 left join saletargets as st
  on st_im.saletarget_hospital_id = st.saletarget_hospital_id
 and st_im.saletarget_product_id = st.saletarget_product_id
 and st.deleted = 0
   set st_im.record_id = if(st.id is not null, st.id, st_im.record_id)
 where st_im.import_id = 383

The problem is the LEFT JOIN , st.deleted can be NULL , so the condition if(st.deleted = 0) will result in NULL as well , conditions on the right table of a left join should be placed inside the ON clause .
Then, you can assume that when st.id is not null is met, st_deleted = 0 is also true, so you only need to check the first condition.

Upvotes: 1

Related Questions