Inserting rows depending on fields

Question

I have a temp table, Temp_Table, and depending on its two fields I need to insert rows in other tables, Table1 and Table2

These are the conditions to achieve these,

If Temp_Table.Field1 = Table1.Field1 and Temp_Table.Field2 > Table1.Field2 Then, move the row from Table1 to Table2 and copy row from Temp_Table to Table1.
If Temp_Table.Field1 = Table1.Field1 and Temp_Table.Field2 < Table1.Field2 Then, copy row from Temp_Table to Table2.
If Temp_Table.Field1 doesn't find any match in Table1, copy the row from Temp_Table to Table1.

As both Table1 and Table2 have triggers, output clause is of no use. Temp_Table itself might have duplicate values for Field1 so check needs to be done for each row.

How can I achieve this by mySql only?

I will try to show some sample data. All tables don't have autogenerated id.

Table1:

Id         Field1          Field2           Field3
0            1             30 Dec 2016        data1

Table2:

Id        Field1           Field2          Field3

Temp_Table:

Id        Field1           Field2          Field3
1           1              29 Dec 2016      data2
2           2             31 Dec 2016       data3
3           2             01 Jan 2017       data4

Result tables: Table1:

Id       Field1        Field2          Field3
0         1            30 Dec 2016     data1
3         2            01 Jan 2017     data4

Tabl2:

Id         Field1          Field2          Field3
1            1           29 Dec 2016       data2
2            2           31 Dec 2016       data3

@GordonLinoff, thank you for reminding. I have changed tags accordingly. — Nets
Please show us some sample data and what the final result should look like. — Vladimir Baranov
did you try a simple INSERT/SELECT? Something like insert into <destination> select <whatever> from <source> where <filter> — mauro
@mauro, yes, but that 'filter' for 'where' clause is giving me problem. — Nets

user3112728 user3112728 · Accepted Answer · 2017-03-19T17:04:22

It sounds like there are a couple operations that need to be performed together transactions are a great way of making them all work or all fail.

I've only provided pseudo queries, because, the actual queries used will likely evolve as you get used to whatever it's your trying to do.

Concepts:

Stored Procedure
TRY/CATCH block
Begin/Commit/Rollback Tran
Left Join Query to detect missing records

Example:

begin proc Abc as
begin
  begin try
    begin tran
      --Step 1
      Insert Table2 select Table1
      Delete from Table1
      insert into Table1 select TempTable
      --Step 2
      insert into Table2 select TempTable
      --Step 3
      insert into Table1 select TempTable left join Table1
    commit tran
  end try
  begin catch
    rollback tran
  end catch
end

Inserting rows depending on fields

1 Answers