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,

  1. 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.

  2. If Temp_Table.Field1 = Table1.Field1 and Temp_Table.Field2 < Table1.Field2 Then, copy row from Temp_Table to Table2.

  3. 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

Show source
| sql-server   | sql   | sql-server-2014   2016-12-31 21:12 0 Answers

Answers ( 0 )

◀ Go back