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.
Can we use Delete with Merge
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Can we use Delete with Merge
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...
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Can we use Delete with Merge
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 4
- Joined: Mon Feb 25, 2013 10:34 am
Re: Can we use Delete with Merge
Thanks for your reply...
So finally we cannot use DELETE in Merge....
So finally we cannot use DELETE in Merge....