delete with inner join

Moderator: NorbertKrupa

Post Reply
evaleah
Newbie
Newbie
Posts: 3
Joined: Fri Jan 30, 2015 8:03 pm

delete with inner join

Post by evaleah » Fri Feb 06, 2015 7:53 pm

I want to do a delete with an inner join and the documentation says:
DELETE statements support subqueries and joins, which is useful for deleting values in a table
based on values that are stored in other tables. See the Examples section below.
But when I run this

Code: Select all

delete aa.* from account_metric_agg aa
inner join metric_agg_stg_csi c on c.csi = account_metric_agg.csi and c.accmess = 1
where aa.updatetime < '2015-02-06 11:31:36'
I get

Code: Select all

DELETE FROM aa. not successful
An error occurred when executing the SQL command:
delete aa.* from account_metric_agg aa
inner join metric_agg_stg_csi c on c.csi = account_metric_agg.csi and c.accmess = 1
where aa.updatetime < '2015...

[Vertica][VJDBC](4856) ERROR: Syntax error at or near "aa" [SQL State=42601, DB Errorcode=4856]

Execution time: 0.01s

1 statement failed.
I have tried several varieties on this theme with no luck. The error is always something similar to this. In the examples in the docs there are no joins and my searches are coming up with nothing helpful!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: delete with inner join

Post by JimKnicely » Mon Feb 09, 2015 9:42 pm

Try the delete with a sub query...

Code: Select all

dbadmin=> select * from account_metric_agg;
 csi  |     updatetime
------+---------------------
 test | 2015-02-07 11:31:36
 test | 2015-02-05 11:31:36
(2 rows)

dbadmin=> select * from metric_agg_stg_csi;
 csi  | accmess
------+---------
 test |       1
(1 row)

dbadmin=> delete
dbadmin->   from account_metric_agg
dbadmin->  where exists (select null
dbadmin(>                  from metric_agg_stg_csi c
dbadmin(>                 where c.csi = account_metric_agg.csi
dbadmin(>                   and c.accmess = 1)
dbadmin->    and account_metric_agg.updatetime < '2015-02-06 11:31:36';
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from account_metric_agg;
 csi  |     updatetime
------+---------------------
 test | 2015-02-07 11:31:36
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”