Issues in MERGE INTO Command

[incrediblegiant]

Hi,
Am new to Vertica and trying to get a hang of it. I use Vertica 6.0 and am trying to use MERGE INTO statement but face an issue. Below is the structure of my target table:

Table Name: expenses
ID calendar_month calendar_year Groceries_Expense Movie_Expense Shopping_Expense Travel_Expense Education_Expense
1 12 2012 343 60 890 780 900
1 11 2012 349 20 670 450 300

Each column is calculated from multiple joins across tables and I have seperate sql statements to calculate them. In order to have 1 row per ID with all expenses populated, I executed all SQL statements and populated the following temp table.

Table Name: tempexpenses
ID calendar_month calendar_year Groceries_Expense Movie_Expense Shopping_Expense Travel_Expense Education_Expense
1 12 2012 343
1 12 2012 60
1 12 2012 890
1 12 2012 780
1 12 2012 900
1 11 2012 349
1 11 2012 20
1 11 2012 670
1 11 2012 450
1 11 2012 300

Then I wrote a MERGE INTO statement to get my final record (eg., for groceries expense).

MERGE INTO expenses tgt
USING tempexpenses src
ON tgt.id = src.id
AND tgt.calendar_month = src.calendar_month
AND tgt.calendar_year = src.calendar_year
WHEN MATCHED THEN UPDATE SET
groceries_expense = CASE
WHEN tgt.groceries_expense IS NULL THEN src.groceries_expense
ELSE tgt.groceries_expense
END,
movie_expense = CASE
WHEN tgt.movie_expense IS NULL THEN src.movie_expense
ELSE tgt.movie_expense
END
WHEN NOT MATCHED THEN INSERT(id, calendar_month, calendar_year, groceries_expense, movie_expense) values (src.id,src.calendar_month,src.calendar_year,src.groceries_expense, src.movie_expense);

I will add other expense to the same merge statement. Initially I tried with just 2 expense to see whether it works.

But when I execute the above statement, the o/p looks the same as my temp table, 1 row for id, month, year, each expense.

Can someone point out what am I missing. I tried re-writing in other ways, nothing worked. Any help would be appreciated. Thanks!

[JimKnicely]

Hi incrediblegiant,

Welcome to the forums!

Did you actually run that MERGE command? If you did you should have received an error similar to the following:

ERROR 3147: Duplicate MERGE key detected in join [(testdb.expenses x intersect_wh.tempexpenses) using expenses_b0 and subquery (PATH ID: 1)]; value [1,11,2012]

This error is occurring in the WHEN MATCHED THEN UPDATE SET section of the MERGE command... According to the Vertica documentation:

If Vertica finds more than one matching row in the source table for a row in the target table, you'll see a run-time error

Basically, you can't have duplicate values for the combination of the id, calendar_month and calendar_year columns in your tempexpenses table.

[incrediblegiant]

Hi knicely87,

Now I see why my query did not work. Thanks a lot. I'll see how to re-modify this. I shall get back if I face any issues. Thanks again.

[jagadeesh]

I am getting the Duplicate Merge Key error but i checked my source and target table there are no duplicate records.

[JimKnicely]

Hi,

From your first post, is this is the data in your tempexpenses table?

Code: Select all

dbadmin=> SELECT * FROM tempexpenses;
 ID | calendar_month | calendar_year | Groceries_Expense | Movie_Expense | Shopping_Expense | Travel_Expense | Education_Expense
----+----------------+---------------+-------------------+---------------+------------------+----------------+-------------------
  1 |             12 |          2012 |               343 |               |                  |                |         
  1 |             11 |          2012 |               349 |               |                  |                |         
  1 |             11 |          2012 |               450 |               |                  |                |         
  1 |             12 |          2012 |               343 |               |                  |                |         
  1 |             11 |          2012 |               349 |               |                  |                |         
  1 |             11 |          2012 |               450 |               |                  |                |         
  1 |             12 |          2012 |                60 |               |                  |                |         
  1 |             11 |          2012 |                20 |               |                  |                |         
  1 |             11 |          2012 |               300 |               |                  |                |         
  1 |             12 |          2012 |                60 |               |                  |                |         
  1 |             11 |          2012 |                20 |               |                  |                |         
  1 |             11 |          2012 |               300 |               |                  |                |         
  1 |             12 |          2012 |               890 |               |                  |                |         
  1 |             12 |          2012 |               780 |               |                  |                |         
  1 |             12 |          2012 |               900 |               |                  |                |         
  1 |             11 |          2012 |               670 |               |                  |                |         
  1 |             12 |          2012 |               890 |               |                  |                |         
  1 |             12 |          2012 |               780 |               |                  |                |         
  1 |             12 |          2012 |               900 |               |                  |                |         
  1 |             11 |          2012 |               670 |               |                  |                |         
(20 rows)

If so the following query will show you the duplicate values:

Code: Select all

dbadmin=> SELECT src.id,
dbadmin->        src.calendar_month,
dbadmin->        src.calendar_year,
dbadmin->        COUNT(*)
dbadmin->   FROM tempexpenses src
dbadmin->  GROUP
dbadmin->     BY src.id,
dbadmin->        src.calendar_month,
dbadmin->        src.calendar_year
dbadmin-> HAVING COUNT(*) > 1;
 id | calendar_month | calendar_year | COUNT
----+----------------+---------------+-------
  1 |             12 |          2012 |    10
  1 |             11 |          2012 |    10
(2 rows)

[deepvoice]

I am trying to do something similar but duplicate keys are unavoidable. I am trying to do a summarization of raw logging.

Code: Select all

Raw_table
______
EmpID | Enter/Exit | Zone1 | Zone2 
_____________________________________
bob       | Entered   | 2013-01-01 12:00:00 | null 
bob       | Entered   |  null | 2013-01-01 13:00:00 

The key is Bob

Code: Select all

MERGE INTO security.summary n USING security.raw_log m
	ON m.EmpID = n.EmpID

	WHEN MATCHED THEN UPDATE SET

  		n.last_entered_zone1 = m.zone1,
        n.last_entered_zone2 = m.zone2

 	WHEN NOT MATCHED THEN

	 INSERT (
             n.EmpID = m.EmpID
             n.first_time_entered_zone1 = m.zone1,
             n.first_time_entered_zone2 = m.zone2

);

I am getting an error because of the duplicate key in the raw_log (bob)
Not sure if this is happening on the Update part or the insert part. (I am new to Vertica and still getting up to speed)

I was using this page as a template: http://www.vertica.com/2012/08/27/load- ... more-10474

Is there a way around this in a single statement without having to summarize everything first (more coding) into a temp_table?

*Note: The example is very simplistic but I am inserting about 8K rows a second into a "merge" table (raw_logs) then trying to merge it into a summary table.
Bob does a lot of walking around. So much that it is entirely possible he will be in the temp_table twice or more between merge cycles.

[JimKnicely]

Hi,

If you have duplicate key values in the merge data you'll get the error with both the insert and the update parts of the merge command:

Code: Select all

dbadmin=> select * from jim;
 c1 |      c2       
----+---------------
  1 | Vertica Is Ok
(1 row)

dbadmin=> select * from raw_data;
 c1 |       c2        
----+-----------------
  1 | Vertica Rocks
  1 | Vertica is cool
(2 rows)

Merge with update:

Code: Select all

dbadmin=> merge into jim j using raw_data r
dbadmin->    on j.c1 = r.c1
dbadmin->    when matched then update set
dbadmin->      c2 = r.c2;
ERROR 3147:  Duplicate MERGE key detected in join [(public.jim x public.raw_data) using jim_b0 and subquery (PATH ID: 1)]; value [1]

Merge with insert:

Code: Select all

dbadmin=> merge into jim j using raw_data r
dbadmin->    on j.c1 = r.c1
dbadmin->    when not matched then insert values
dbadmin->      (r.c1, r.c2);
ERROR 3147:  Duplicate MERGE key detected in join [(public.jim x public.raw_data) using jim_b0 and subquery (PATH ID: 1)]; value [1]

The error occurs on the join.