There are three tables - Table1 , Table2 , Table3 . The tables are absolutely identical in structure, for example, the fields are number_tasks (key field, no matches), customer, task . Table1 is loaded every day from an external source, it has records that are not in Table2 , and in Table2 there are records that are not in Table1 , Table2 is the result. Table1 there are missing entries in Table2 - new tasks. In Table2 there are records that are not in Table1 - solved problems.

Required: in Table2 move the solved tasks to Table3 (add to existing ones) and add new ones from Table1 to Table2 .

"If in Table2 there are no records from Table1, then we add them to Table2 and if in Table1 there are no records from Table2, then from Table2 we transfer them to Table3"

  • Do you want to do this in one request? It is logical to make two insert into from select queries, first 2 β†’ 3 and then 1 β†’ 2. - Alex Krass
  • I can not understand how to issue a request exactly, - "and if in Table1 there are no records from Table2, then from Table2 we transfer them to Table3". Sorry, it's just completely new to SQL. - Eugene
  • On the first part, if I understood correctly, then: INSERT INTO Table2 SELECT Table1. * FROM Table1 LEFT JOIN Table2 ON Table1.Title number = Table2.Title number WHERE Table1.Title number IS NULL - Eugene
  • Not two requests, but three. Another removal request from table 2. - Akina

1 answer 1

 INSERT INTO [Π’Π°Π±Π»ΠΈΡ†Π°3] ([НомСр Π·Π°Π΄Π°Ρ‡ΠΈ], [Π—Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊ], [Π—Π°Π΄Π°Ρ‡Π°]) SELECT [Π’Π°Π±Π»ΠΈΡ†Π°2].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ], [Π’Π°Π±Π»ΠΈΡ†Π°2].[Π—Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊ], [Π’Π°Π±Π»ΠΈΡ†Π°2].[Π—Π°Π΄Π°Ρ‡Π°] FROM [Π’Π°Π±Π»ΠΈΡ†Π°2] LEFT JOIN [Π’Π°Π±Π»ΠΈΡ†Π°1] ON [Π’Π°Π±Π»ΠΈΡ†Π°2].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] = [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] WHERE [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] IS NULL; DELETE [Π’Π°Π±Π»ΠΈΡ†Π°2].* FROM [Π’Π°Π±Π»ΠΈΡ†Π°2] WHERE [Π’Π°Π±Π»ΠΈΡ†Π°2].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] IN ( SELECT t2.[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] FROM [Π’Π°Π±Π»ΠΈΡ†Π°2] AS t2 LEFT JOIN [Π’Π°Π±Π»ΠΈΡ†Π°1] ON t2.[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] = [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] WHERE [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] IS NULL ); INSERT INTO [Π’Π°Π±Π»ΠΈΡ†Π°2] ([НомСр Π·Π°Π΄Π°Ρ‡ΠΈ], [Π—Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊ], [Π—Π°Π΄Π°Ρ‡Π°]) SELECT [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ], [Π’Π°Π±Π»ΠΈΡ†Π°1].[Π—Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊ], [Π’Π°Π±Π»ΠΈΡ†Π°1].[Π—Π°Π΄Π°Ρ‡Π°] FROM [Π’Π°Π±Π»ΠΈΡ†Π°1] LEFT JOIN [Π’Π°Π±Π»ΠΈΡ†Π°2] ON [Π’Π°Π±Π»ΠΈΡ†Π°2].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] = [Π’Π°Π±Π»ΠΈΡ†Π°1].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] WHERE [Π’Π°Π±Π»ΠΈΡ†Π°2].[НомСр Π·Π°Π΄Π°Ρ‡ΠΈ] IS NULL;

Ps. It is assumed that in Table 2 there are NO entries present in Table 3. Otherwise, an additional restriction is needed in the first query.

  • It seems that I understood, but when I try to repeat your query in the Access database, it gives me an error, β€œCharacters detected outside the SQL statement”. - Eugene
  • @ Eugene This is THREE requests. DIFFERENT REQUEST. And you have to create THREE requests. And perform them STRICTLY in the order indicated. - Akina
  • I understand, but the removal request does not want to work. - Eugene
  • Strange, but I get tables 1 and 2 absolutely identical, theoretically it is possible, but not realistic. And with the removal figured out, it's my hands curves. - Eugene
  • After completing the queries, SO MUST BE !!! If there is something in table 1 that is not in table 2 - it worked incorrectly. If there are no records from Table1 in Table2, then we add them to Table2 ; if in Table 2 there is something left that is not in table 1, it worked incorrectly if Table1 no records from Table2, then from Table2 we transfer them to Table3 . Conclusion - after processing, tables 1 and 2 should be identical. - Akina