I have table LOCNAG_ENT and jsut for test i cloned this table and changed some ENTERPRISE_ID for exact LOCACCESSGRP_ID and trying to merge.

But when it goes to DELETE i have "syntax error near 'DELETE' on line 5'
And i can't catch why?
And also do we have some similar behaviour like in SLQ Server? For "WHEN MATCH BY SOURCE"


MERGE INTO LOCNAG_ENT as tgt
USING LOCNAG_ENT_CLONE src
on src.LOCACCESSGRP_ID=tgt.LOCACCESSGRP_ID and src.ENTERPRISE_ID=tgt.ENTERPRISE_ID
when matched then skip
WHEN NOT MATCHED and src.ENTERPRISE_ID <> tgt.ENTERPRISE_ID and src.LOCACCESSGRP_ID <> tgt.LOCACCESSGRP_ID THEN DELETE
WHEN NOT MATCHED and tgt.LOCACCESSGRP_ID <> src.LOCACCESSGRP_ID and tgt.ENTERPRISE_ID <> src.ENTERPRISE_ID THEN INSERT (LOCACCESSGRP_ID, ENTERPRISE_ID) values(src.LOCACCESSGRP_ID, src.ENTERPRISE_ID)


How can i procceed with logic if i have same values from both tables (agrp_id, enterpsie_id) then just skip, if i have new values in source table but doesn't have in target, then insert, if i doesn't have values in source table which have target table, then delete them?

asked 22 Nov, 10:08

Taifunov's gravatar image

Taifunov
112
accept rate: 0%

edited 22 Nov, 11:18


DELETE is not permitted in a NOT MATCH.

permanent link

answered 22 Nov, 10:38

Chris%20Keating's gravatar image

Chris Keating
6.2k38104
accept rate: 30%

How then can i procceed with logic if i have same values from both tables (agrp_id, enterpsie_id) then just skip, if i have new values in source table but doesn't have in target, then insert, if i doesn't have values in source table which have target table, then delete them?

(22 Nov, 11:01) Taifunov
1

A second MERGE that deals with the DELETE but formed as a WHEN MATCHED would be an option.

(22 Nov, 21:43) Chris Keating
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7
×2

question asked: 22 Nov, 10:08

question was seen: 56 times

last updated: 22 Nov, 21:43