0

I am trying to check for the ID. If it does not exist I want my data to be inserted as a new record in table and if ID exists I want to compare both incoming new record and existing record and only update the values which are null in existing record. I have written the query but I don't know how to check for null in each column:

Here is the query:

insert into ui_table(MDMID,FirstName,Lastname,Mail,Address,City,State,Zip,ProfileStatus,TAID)
select im.MDMID,im.FirstName,im.Lastname,im.Mail,im.Address,im.City,im.State,im.Zip,im.ProfileStatus,im.TAID
from intermediate im on duplicate key update 
    MDMID = im.MDMID, 
    FirstName = im.FirstName,
    Lastname = im.Lastname,
    Mail = im.Mail,
    Address = im.Address,
    City = im.City,
    State = im.State,
    Zip = im.Zip,
    ProfileStatus = im.ProfileStatus,
    TAID = im.TAID;
Stu
  • 30,392
  • 6
  • 14
  • 33
NIKI
  • 1
  • 3
  • `ODKU column = COALESCE(column, im.column)`? – Akina Aug 15 '22 at 08:41
  • insert into ui_table(MDMID,FirstName,Lastname,Mail,Address,City,State,Zip,ProfileStatus,TAID) select im.MDMID,im.FirstName,im.Lastname,im.Mail,im.Address,im.City,im.State,im.Zip,im.ProfileStatus,im.TAID from intermediate im on duplicate key update ui_table.MDMID = IFNULL(NULLIF( ui_table.MDMID,''),im.MDMID); – NIKI Aug 15 '22 at 09:19
  • I have used this one is this correct? – NIKI Aug 15 '22 at 09:19
  • You tell ".. and **only** update the values **which are null** in existing record". So you do not need to update empty string, and NULLIF() usage is not correct. – Akina Aug 15 '22 at 09:21

0 Answers0