I am trying to compare 2 ID's from different tables and if they don't match the row that isn't in the source should be added.
But I am getting this error:
PLS-00103: Encountered the symbol "Person" when expecting one of the following: : = ( @ % ;
Code:
CREATE OR REPLACE PROCEDURE merge_test AS
BEGIN
MERGE person AS p
USING person_test AS t
ON t.person_id = p.person_id
WHEN NOT MATCHED BY TARGET THEN
INSERT(person_id,vorname, nachname, mobil, telefon, fax, e_mail, fgh_id)
VALUES(t.person_id, t.vorname, t.nachname, t.mobil, t.telefon, t.fax, t.e_mail, t.fgh_id);
END;
/
Maybe my Syntax is wrong or i have to add an "execute immediate"? I am pretty new to SQL and PL/SQL.
There are some issues in your MERGE
statement :
t.person_id = p.person_id
into parenthesesAS
keyword. Get rid of
themUsing person_test as t
) is not possible, but use a
subquery(Using (select ... from person_test) t
)by target
from When not matched by target then
MERGE
statement needs to be followed by an INTO
clauset.person_id = p.person_id
after ON
clause should be parenthesedSo use:
MERGE INTO person p
USING (SELECT person_id,
vorname,
nachname,
mobil,
telefon,
fax,
e_mail,
fgh_id
from person_test) t
ON (t.person_id = p.person_id)
WHEN NOT MATCHED
THEN INSERT(person_id, vorname, nachname, mobil, telefon, fax, e_mail, fgh_id)
VALUES(t.person_id, t.vorname, t.nachname, t.mobil, t.telefon, t.fax, t.e_mail, t.fgh_id);