Trouble Aliasing a Table in a DELETE Statement

[becky]

Hi all,

I need to delete data from a table using a correlated sub-query. I'm having an issue with aliasing the table which has the data I want purged.

Code: Select all

dbadmin=> DELETE FROM store_location_dim sld WHERE NOT EXISTS (SELECT NULL FROM store_location_ext_dim sled WHERE sled.loc_id = sld.loc_id);
ERROR 4856:  Syntax error at or near "sld" at character 32
LINE 1: DELETE FROM store_location_dim sld WHERE NOT EXISTS (SELECT ...
                                       ^
dbadmin=>

Can we not alias this way? This works okay in Oracle.

[id10t]

Hi!

Try this:

Code: Select all

DELETE FROM store_location_dim WHERE NOT EXISTS (SELECT NULL FROM store_location_ext_dim sled WHERE store_location_dim.loc_id = sled.loc_id);

PS

Code: Select all

vsql=> select * from t1;
 id | val 
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

Code: Select all

vsql=> select * from t2;
 id | val 
----+-----
  1 | foo
  4 | egg
(2 rows)

Code: Select all

vsql=> delete from t1 where not exists (select null from t2 as Q where t1.id = Q.id);
 OUTPUT 
--------
      2
(1 row)

Code: Select all

vsql=> select * from t1;
 id | val 
----+-----
  1 | foo

[becky]

Thanks, skwa! That was an easier solution than I expected I guess Vertica doesn't need the alias. Although, I wish they would allow for it.