Can we use Delete with Merge

[srinivas.martha]

Hi all,

I have created two tables Z_Location and Z_New_Location with following structure:

create table edw_pro.Z_Location(UID Numeric(3), col1 Numeric(5,2), col2 Numeric(5,2),Cnt Numeric(2),Name Char(15))
create table edw_pro.Z_New_Location(UID Numeric(3), col1 Numeric(5,2), col2 Numeric(5,2),Cnt Numeric(2),Name Char(15))

And inserted few records in both the tables:

select * from edw_pro.Z_Location

UID col1 col2 Cnt Name
1 4.10 7.70 1 Donald
1 10.10 2.70 1 BurgerKing
2 4.10 7.70 1 CarWash

select * from edw_pro.Z_New_Location

UID col1 col2 Cnt Name
2 5.10 7.90 1 CarWash
3 4.10 7.70 1 Donald
1 10.10 2.70 1 BurgerKing

I used MERGE Statement to Load New data and Modify Existing:

MERGE INTO edw_pro.Z_Location tgt
USING edw_pro.Z_New_Location src
ON src.uid = tgt.uid
AND src.col1 = tgt.col1
AND src.col2 = tgt.col2
WHEN MATCHED THEN
UPDATE SET cnt = tgt.cnt + src.cnt
WHEN NOT MATCHED THEN
INSERT VALUES (src.uid, src.col1, src.col2, src.cnt, src.name);

and above statement executed successfully and final result after MERGE is :

select * from edw_pro.Z_Location

UID col1 col2 Cnt Name
2 4.10 7.70 1 Donald
2 5.10 7.90 1 CarWash
3 4.10 7.70 1 Donald
1 4.10 7.70 1 Donald
1 10.10 2.70 2 BurgerKing

But i want to Use Delete instead of Update.
Is there any way to use delete statement in MERGE :

I tried with below statements:

MERGE INTO edw_pro.Z_Location tgt
USING edw_pro.Z_New_Location src
ON src.uid = tgt.uid
AND src.col1 = tgt.col1
AND src.col2 = tgt.col2
WHEN MATCHED THEN
UPDATE SET cnt = tgt.cnt + src.cnt
Delete from edw_pro.Z_Location
--UPDATE SET cnt = tgt.cnt + src.cnt
WHEN NOT MATCHED THEN
INSERT VALUES (src.uid, src.col1, src.col2, src.cnt, src.name);
--------------------------------
MERGE INTO edw_pro.Z_Location tgt
USING edw_pro.Z_New_Location src
ON src.uid = tgt.uid
AND src.col1 = tgt.col1
AND src.col2 = tgt.col2
WHEN MATCHED THEN
UPDATE SET cnt = tgt.cnt + src.cnt
Delete where col1>4
--UPDATE SET cnt = tgt.cnt + src.cnt
WHEN NOT MATCHED THEN
INSERT VALUES (src.uid, src.col1, src.col2, src.cnt, src.name);

ERROR 4856: Syntax error at or near "Delete" at character 190

So finally can we use Delete instead of Update in MERGE statement?
Or can we use Delete along with Update in MERGE statement?

Note: This is just example to know whether we can use Delete in Merge or not.

Thanks in advance.

[JimKnicely]

Hi,

The MERGE command in Vertica does not support a delete operation.

Can you run a DELETE command prior to the MERGE or after the MERGE depending on your requirements?

For instance...

Code: Select all

dbadmin=> select * from Z_Location;
 uid | col1  | col2 | cnt |    name
-----+-------+------+-----+------------
   1 |  4.10 | 7.70 |   1 | Donald
   1 | 10.10 | 2.70 |   1 | BurgerKing
   2 |  4.10 | 7.70 |   1 | CarWash
(3 rows)

dbadmin=> select * from Z_new_Location;
 uid | col1  | col2 | cnt |    name
-----+-------+------+-----+------------
   1 | 10.10 | 2.70 |   1 | BurgerKing
   2 |  5.10 | 7.90 |   1 | CarWash
   3 |  4.10 | 7.70 |   1 | Donald
(3 rows)

dbadmin=> DELETE FROM Z_Location
dbadmin->  WHERE EXISTS (SELECT NULL
dbadmin(>                   FROM Z_New_Location src
dbadmin(>                  WHERE src.uid = uid
dbadmin(>                    AND src.col1 = col1
dbadmin(>                    AND src.col2 = col2
dbadmin(>                    AND col1 > 4);
 OUTPUT
--------
      3
(1 row)

dbadmin=> select * from Z_Location;
 uid | col1 | col2 | cnt | name
-----+------+------+-----+------
(0 rows)

dbadmin=> MERGE INTO Z_Location tgt
dbadmin-> USING Z_New_Location src
dbadmin->    ON src.uid = tgt.uid
dbadmin->   AND src.col1 = tgt.col1
dbadmin->   AND src.col2 = tgt.col2
dbadmin->  WHEN NOT MATCHED THEN
dbadmin->    INSERT VALUES (src.uid, src.col1, src.col2, src.cnt, src.name);
 OUTPUT
--------
      3
(1 row)

dbadmin=> select * from Z_Location;
 uid | col1  | col2 | cnt |    name
-----+-------+------+-----+------------
   1 | 10.10 | 2.70 |   1 | BurgerKing
   2 |  5.10 | 7.90 |   1 | CarWash
   3 |  4.10 | 7.70 |   1 | Donald
(3 rows)

[JimKnicely]

Or maybe this is a better solution?

Code: Select all

dbadmin=> SELECT * FROM z_location;
 uid | col1  | col2 | cnt |    name
-----+-------+------+-----+------------
   1 |  4.10 | 7.70 |   1 | Donald
   1 | 10.10 | 2.70 |   1 | BurgerKing
   2 |  4.10 | 7.70 |   1 | CarWash
(3 rows)

dbadmin=> SELECT * FROM z_new_location;
 uid | col1  | col2 | cnt |    name
-----+-------+------+-----+------------
   1 | 10.10 | 2.70 |   1 | BurgerKing
   2 |  5.10 | 7.90 |   1 | CarWash
   3 |  4.10 | 7.70 |   1 | Donald
(3 rows)

dbadmin=> MERGE INTO Z_Location tgt
dbadmin-> USING Z_New_Location src
dbadmin->    ON src.uid = tgt.uid
dbadmin->   AND src.col1 = tgt.col1
dbadmin->   AND src.col2 = tgt.col2
dbadmin-> WHEN MATCHED THEN
dbadmin->   UPDATE SET name = CASE
dbadmin->                       WHEN tgt.col1 > 4 THEN 'DELETE'
dbadmin->                       ELSE tgt.name
dbadmin->                     END
dbadmin-> WHEN NOT MATCHED THEN
dbadmin->    INSERT VALUES (src.uid, src.col1, src.col2, src.cnt, src.name);
 OUTPUT
--------
      3
(1 row)

dbadmin=> DELETE FROM Z_Location WHERE name = 'DELETE';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM z_location;
 uid | col1 | col2 | cnt |  name
-----+------+------+-----+---------
   1 | 4.10 | 7.70 |   1 | Donald
   2 | 4.10 | 7.70 |   1 | CarWash
   2 | 5.10 | 7.90 |   1 | CarWash
   3 | 4.10 | 7.70 |   1 | Donald
(4 rows)

[srinivas.martha]

Thanks for your reply...

So finally we cannot use DELETE in Merge....