Why Unsafe query: Delete statement without where clears all data in the table?

Question

I have the following query in Redshift:

delete * from a
left join s on a.order_id = s.id
where s.order_id is not null

When I try to execute it, it gives me back the warning of the title. However, it does have a where clause, so I am wondering why this appear.

Maybe it is because the where clause makes reference to the query of the join and not the other? If so, how could avoid this warning?

I removed the postgresql because no such warning exists there. — user330315
– user330315, Commented Nov 12, 2019 at 14:51

John Rotenstein · Accepted Answer · 2019-11-13 03:21:51Z

2

You could attempt to rewrite it, something like:

DELETE FROM a
WHERE a.order_id IN (SELECT DISTINCT id FROM other_table)

answered Nov 13, 2019 at 3:21

John Rotenstein

273k28 gold badges456 silver badges541 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pang · Accepted Answer · 2020-06-04 02:05:27Z

1

Turns out the solution (for maintaining the join) was to delete the *.

delete from a
left join s on a.order_id = s.id
where s.order_id is not null

edited Jun 4, 2020 at 2:05

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Nov 20, 2019 at 14:24

Javier Lopez Tomas

2,4027 gold badges31 silver badges55 bronze badges

1 Comment

KT12 Over a year ago

I'm trying to compose a similar delete statement in Redshift, except looking for rows that do not exist in s by using WHERE s.order_id IS NULL but I can't seem to get it to work.

Collectives™ on Stack Overflow

Why Unsafe query: Delete statement without where clears all data in the table?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related