Reputation: 141
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
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