Delete query on view - redshift

Question

I have the following query:

DELETE FROM registration_null_imei_reconcile_view_final WHERE token in (SELECT token from registration_not_null_imei_reconcile_view)

it is giving following exception

java.sql.SQLException: Amazon Invalid operation: cannot delete from a view;

Can't we delete data from redshift views??

AlexYes AlexYes · Accepted Answer · 2017-07-24T11:37:12

A view is a just a shortcut for a SQL query, you can't delete from a view. You have to delete from the underlying table that has token column. If you want to use the view because there are some additional filters that are applied to this table you can join this table to the view in delete statement like this:

delete from your_table t
using registration_null_imei_reconcile_view_final v
where t.id=v.id
and t.token in (SELECT token from registration_not_null_imei_reconcile_view)

Delete query on view - redshift

1 Answers