CODEWITHSUNDEEP
postgresql
Alimin
Alimin

Reputation: 2717

postgresql delete with select condition

I am trying to delete data by using subquery after condition, but my trick fail when my sql script is executed this is my script:

--trying to insert subquery after "=" operator

delete from wni_layanan where id = SELECT (currval('wni_layanan_id_seq'));

this is error message who apears in my sqleditor after script executed

ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 36

how to delete with select condition in postgresql ? please give me solution to solve my problem. thank you

Upvotes: 0

Views: 3563

Answers (1)

user330315
user330315

Reputation:

You need to wrap the whole sub-select in parentheses. You also should not wrap a "standalone" function call (or expression) like currval('..') in parentheses. 

delete from wni_layanan 
where id = (SELECT currval('wni_layanan_id_seq') );

The above will only work correctly if the subselect returns exactly one row (and one column). When using currval() this is guaranteed. If your sub-query returns several rows you need to use IN (..)

delete from wni_layanan 
where id IN (SELECT ... );

But for your example you don't need the sub-select at all:

delete from wni_layanan 
where id = currval('wni_layanan_id_seq');

Upvotes: 1

Related Questions