merge targett [ alias ]
using /* targett join */ sourcet st
on /* tt.*/ field1=st.field1...
when matched then
update /* targett */ set field2=st.field2...
when not matched then
insert /* into targett */ (field2...)
values ( field2... )
;
Essentially you do not (as you would with update...from or delete...from) specify the target table where it could be assumed. A join between target and source is both assumed and required.
NB that an alias for targett is allowed, and sometimes required for disambiguation.
You can't put WHERE clauses in the join part, but you can put eg "AND age > 70" after the WHEN (NOT) MATCHED bits.
MERGE (with expectations omitted for clarity)
merge targett [ alias ]
using sourcet st
on field1=st.field1...
when matched then
update set field2=st.field2...
when not matched then
insert (field2...)
values ( field2... )
;
NB that unlike traditional SQL statements, MERGE has to be followed by a semicolon. If using DbVisualizer, make sure that neither of the Statement Delimiter options is set to ';', as DbV will interpret it as a 'go' if so.
If you need to tighten the join between sourcet and targett, eg to include only records matching a profile, do it with an 'AND' clause after each 'MATCHED'. It's no good putting such conditions in the join itself.
The using clause doesn't just have to be a table name, it can be a subquery:
using (select * from ... ) st
