Reputation: 10888
I have an existing table called TextData with fields TextId and Text. In below statement, I am trying to merge(Insert/Update) some records into this table using -
MERGE INTO maestro.TEXTDATA T
USING (
select N'/Common/UserStatusExpired', N'Expired' from dual
union all select N'/Common/UserStatusPwdExpired', N'Pwd Expired' from dual
) AS Source (Id, Txt) ON (T.TEXTID = Source.Id)
WHEN MATCHED THEN
UPDATE SET TEXT = Source.Txt
WHEN NOT MATCHED THEN
INSERT (TEXTID, TEXT) VALUES(Source.Id, Source.Txt);
However, getting this error -
missing ON keyword
Can anyone please suggest what I am missing in the Merge statement.
Thank you!
Upvotes: 5
Views: 28738
Reputation: 1
MERGE INTO maestro.TEXTDATA T USING ( select N'/Common/UserStatusExpired' id, N'Expired' txt from dual union all select N'/Common/UserStatusPwdExpired' id, N'Pwd Expired' txt from dual ) source ON (T.TEXTID = Source.Id) WHEN MATCHED THEN UPDATE SET TEXT = Source.Txt WHEN NOT MATCHED THEN INSERT (TEXTID, TEXT) VALUES(Source.Id, Source.Txt)
Upvotes: 0
Reputation: 222722
That would be:
MERGE INTO textdata t
USING (
SELECT N'/Common/UserStatusExpired' AS textid, N'Expired' AS text FROM DUAL
UNION ALL SELECT N'/Common/UserStatusPwdExpired', N'Pwd Expired' FROM DUAL
) s ON (t.textid = s.textid )
WHEN MATCHED THEN
UPDATE SET text = s.text
WHEN NOT MATCHED THEN
INSERT (textid, text) VALUES(s.textid, s.text);
Rationale:
Oracle does not support AS
to define table aliases - you need to remove that keyword
The column names must be defined within the subquery
I also aligned the column names between the source and the target table so the query is easier to follow.
Upvotes: 5
Reputation: 3212
The problem is the "as" clause you've specified. Looks like you want to name your columns in the union'd select lists.
MERGE INTO maestro.TEXTDATA T
USING (
select N'/Common/UserStatusExpired' id, N'Expired' txt from dual
union all select N'/Common/UserStatusPwdExpired' id, N'Pwd Expired' txt from dual
) source ON (T.TEXTID = Source.Id)
WHEN MATCHED THEN
UPDATE SET TEXT = Source.Txt
WHEN NOT MATCHED THEN
INSERT (TEXTID, TEXT) VALUES(Source.Id, Source.Txt)
The railroad diagram shows there is no such "as" clause.
Upvotes: 0
Reputation: 199
I think the (Id, Txt)
shouldn't be there as they were.
MERGE INTO maestro.TEXTDATA T
USING (
select N'/Common/UserStatusExpired' id, N'Expired' txt from dual
union all
select N'/Common/UserStatusPwdExpired' id, N'Pwd Expired' txt from dual
) AS Source ON (T.TEXTID = Source.Id)
WHEN MATCHED THEN
UPDATE SET TEXT = Source.Txt
WHEN NOT MATCHED THEN
INSERT (TEXTID, TEXT) VALUES(Source.Id, Source.Txt);
Upvotes: 0