, , ,

Merge into in SQL

The merge command syntax is
merge into table1
using table2 on (join_condition)
when matched update set col1 = value
when not matched insert (column_list) values (column_values).

The statement components work in the following way:
1. In the merge into table1 clause, you identify a table into which you would like to update data in an existing row or add new data if the row doesn’t already exist as table1.

2. In the using table2 clause, you identify a second table from which rows will be drawn in order to determine if the data already exists as table2. This can be a different table or the same table as table1. However, if table2 is the same table as table1, or if the two tables have similar columns, then you must use table aliases to preface all column references with the correct copy of the table. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
In the on (join_condition) clause, you define the join condition to link the two tables together. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax when referencing columns in the join or filter conditions. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
3. In the when matched then update set col1 = value clause, you define the column(s) Oracle should update in the first table if a match in the second table is found. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax when referencing columns in the update operation. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
4. In the when not matched then insert (column_list) values (value_list) clause, you define what Oracle should insert into the first table if a match in the second table is not found. If table2 in the using clause is the same table as table1 in the merge into clause, or if the two tables have similar columns, then you must use table aliases or the table.column syntax to preface all column references in column_list. Otherwise, Oracle will return an error stating that your column references are ambiguously defined.
Example
Consider the following scenario. Say you manage a movie theater that is part of a national chain. Everyday, the corporate headquarters sends out a data feed that you put into your digital billboard over the ticket sales office, listing out all the movies being played at that theater, along with showtimes. The showtime information changes daily for existing movies in the feed.
merge into movies M1
using movies M2 on (M2.movie_name = M1.movie_name and M1.movie_name = ‘GONE WITH THE WIND’)
when matched then update set M1.showtime = ‘7:30 PM’
when not matched then insert (M1.movie_name, M1.showtime) values (‘GONE WITH THE WIND’,’7:30 PM’);

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply