-2

I have three tables:

Table-1: Column1 | Column2 | Column 3 
Table-2: Column4 | Column5 | ColumnUpdate
Table-3: Column7 | Column8

I need to copy some rows from Table-2 to Table-3 based on some conditions with Table-1:

My insert-statement looks like this:

INSERT INTO Table-3 (
   Column7, 
   Column8)
SELECT Table-2.COLUMN4, Table-2.COLUMN5 
FROM Table-2 INNER JOIN Table-1
ON Table-2.COLUMN4 = TABLE-1.Column1;

However I want to update column: ColumnUpdate" (Table-2) of the selecting row to "1".

So I select some rows and immediately want to update a process column in that row as '1'.

I don't know how to do that. I saw some examples with "OUTPUT" clause or "UPDATE FOR" but I dont exactly know how to use them in my statement.

NoName123
  • 137
  • 5
  • 20
  • Just pull up documentation on the `UPDATE` statement (which is very easy to find). – David Makogon Jul 15 '17 at 15:13
  • Thx for you fast reply, I did some research before I posted the question. I don't know how to incorporate the Update-statement in my statement to achieve this. Basically, I want to update the selected row that I copied, so it is marked as "copied" – NoName123 Jul 15 '17 at 15:20
  • This may be help you: [https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – mehmet sahin Jul 15 '17 at 15:28

1 Answers1

1
MERGE 
   INTO target_table t1
   USING (SELECT col1, col2
               FROM source_table 
               WHERE //conditions here) s1
   ON (t1.id = s1.id)
   WHEN MATCHED THEN 
     UPDATE SET column_update = '1'
   WHEN NOT MATCHED THEN 
     INSERT (col1, col2)
     VALUES (s1.col1, s1.col2)
     WHERE (// condition here);

UPDATE 

BEGIN
   FOR temp_var IN (
     SELECT * from table_a
     WHERE table_a.col1 = table_b.col1)
   LOOP
       // INSERTING INTO TARGET TABLE
       INSERT INTO table_b
       VALUES(temp_var.col1, temp_var.col2);
       // UPDATING SOURCE TABLE
       UPDATE table_a
       SET status = 'COPIED'
       WHERE col1 = temp_var.col1;
   END LOOP;
END;
fg78nc
  • 4,774
  • 3
  • 19
  • 32
  • Thx, however i need to implement a different logic or maybe I am wrong. I need to update the column on the source table when I "copy" the selected row from the source table, so it is marked as "copied"... So when I insert a new row to the target table, then I want to mark this row at the source table as "copied".. So i need to update this column after the statement "WHEN NOT MATCHED THEN", because in this clause the new row will be inserted in the target table – NoName123 Jul 16 '17 at 13:06
  • What is the condition for copying? – fg78nc Jul 16 '17 at 13:33
  • Sry for replying so late (i'm having issues with my computer...bluescreen). I have two tables (let's say A and B) with nearly same data. The difference between these tables is that one of them contains more information (more columns). I need to copy the data from this table (A or B) into a third table (C) based on the condition that (A.id = b.id). When I copy the row..I must mark the row as copied in the source table (By setting the column "update_column" to 1) – NoName123 Jul 16 '17 at 13:57
  • I am not sure if it is feasible with SQL because each statement (one statement for INSERT and other for UPDATE) has to be executed in sequence, which means you can't split it row by row. This can be done with procedural code , PL/SQL. See update above. – fg78nc Jul 18 '17 at 03:00