0

I have the following conditions before inserting into a table -> if the id already exists in the table1, then we need to get the latest record from table2 based on timestamp and check if balance,duedate,status,deleted fields are changed are not. If there is any change in the above 4 fields, then we need to insert into table1 else don't insert. ->If invoice does not exist in table1 then we can directly insert into the table1.

I have a draft query for the same

Select true
Case 
when not exists (select id from table1 tb1, table2 tb2 where tb1.id = tb2.uuid )then 
Insert into table1
values (tb2.tenant_id,tb2.uuid,ba.id,tb2.totalAmount,tb2.total_amount_without_tax,tb2.balance,tb2.field_tinyint4_3, ba.currency,tb2.due_date,tb2.deleted,tb2.invoice_date,tb2.status,tb2.invoice_number,"Invoice",tb2.created_on,tb2.updated_on,CURRENT_TIMESTAMP) from table2 tb2 

Else
Insert into table1
values (tb2.tenant_id,tb2.uuid,ba.id,tb2.totalAmount,tb2.total_amount_without_tax,tb2.balance,tb2.field_tinyint4_3, ba.currency,tb2.due_date,tb2.deleted,tb2.invoice_date,tb2.status,tb2.invoice_number,"Invoice",tb2.created_on,tb2.updated_on,CURRENT_TIMESTAMP) from table2 tb2 

where  tb1.id,tb1.balance,tb1.duedate,tb1.deleted, tb1.status in  (select tb1.id,tb1.balance,tb1.duedate,tb1.deleted, tb1.status  from table1 tb1 where max(DATAINGESTIONTIMESTAMP) groupby(tb1.id,tb1.subscriberid) ) AND  (tb2.balance <> tb1.balance OR tb2.due_date <> tb1.duedate OR tb2.deleted <> tb1.deleted OR tb2.status <> tb1.status)

Thanks

just_a_learner
  • 1
  • 1
  • 1
    Have you tried something like this, https://stackoverflow.com/questions/913841/mysql-conditional-insert. If that will not work, you probably need to use a trigger or other procedural code. https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html –  Apr 21 '22 at 08:14

0 Answers0