Have SQL query fail if subquery returns null

Question

If the below subquery finds no records it returns null and sets the action_batch_id as so. Is it possible to have the entire query fail/exit if the subquery returns no records?

UPDATE action_batch_items 
SET action_batch_id = (SELECT id FROM action_batches WHERE id = '123' 
AND batch_count < 1000  AND batch_size < 100000) 
WHERE id = 1234567

UPDATE: Here's the structure (it's ActiveRecord)

CREATE TABLE "public"."action_batches" (
    "id" int8 NOT NULL,
    "action_batch_container_id" int8 NOT NULL,
    "action_message" json,
    "adaptor" json,
    "batch_count" int4 DEFAULT 0,
    "batch_size" int8 DEFAULT 0
)

CREATE TABLE "public"."action_batch_items" (
    "id" int8 NOT NULL,
    "action_batch_id" int8,
    "config" json
)

This might help: http://stackoverflow.com/a/8766815/1073631 -- assuming you have a field to `join` on... Does `action_batch_items` have an `action_batches_id`? — sgeddes, Feb 19 '16 at 02:02
as i didn't get you wrong, but this query is really really weird to me. you update action_batch_id to the result of the subquery, but in your subquery, let's check it out. you select ID and where id = '123'. so no all of the result should be 123 or null? as you asked you expected 123 or no change at all? — Raffaello.D.Huke, Feb 19 '16 at 02:05
@sgeddes it's ActiveRecord doing raw SQL but yes an action_batch_item has an action_batch_id — kreek, Feb 19 '16 at 02:43
Your inner subquery is confusing. If you have `WHERE id = '123'` doesn't this mean that the `id` assigned to `action_batch_id` will always be `123` ? — Tim Biegeleisen, Feb 19 '16 at 02:54

score 3 · Accepted Answer · answered Feb 19 '16 at 02:30

3

create or replace function raise_error(text) returns int as $$
begin
  raise exception '%', $1;
  return -1;
end; $$ language plpgsql;

and then

action_batch_id = coalesce((select id ...), raise_error('No data'));

answered Feb 19 '16 at 02:30

Abelisto

14,826
2
33
41

Can the `COALESCE()` function accept multiple records/values as input? – Tim Biegeleisen Feb 19 '16 at 02:42
@TimBiegeleisen Surely not. However `update .. set = (select ...)` does not accept multi-row result too. – Abelisto Feb 19 '16 at 02:47
Doesn't your solution mean that an error would potentially be raised for _every_ record in the `action_batch_items` table? – Tim Biegeleisen Feb 19 '16 at 02:57
@TimBiegeleisen Nope. Only for the first failed. – Abelisto Feb 19 '16 at 03:02
@Abelisto I already put coalesce in every part of my select, but I did not think about it, thank you, my friend! – Fábio Zangirolami Jun 28 '18 at 02:15
1

@FábioZangirolami IMO something like `raise(text)` and `raiseif(condition, text)` should be included to the SQL standard :) Good luck! – Abelisto Jul 01 '18 at 05:42

score 1 · Answer 2 · answered Feb 19 '16 at 01:54

1

Try using COALESCE():

UPDATE action_batch_items 
SET action_batch_id = 
(
    SELECT COALESCE(id, action_batch_id)
    FROM action_batches
    WHERE id = '123' AND batch_count < 1000  AND batch_size < 100000
) 
WHERE id = 1234567

In the event that the subquery returns NULL the action_batch_id column will not be changed. In the case of the subquery returning one or more non NULL records, your UPDATE will behave as before.

answered Feb 19 '16 at 01:54

Tim Biegeleisen

502,043
27
286
360

dude,but action_batch_id is in table action_batch_items, he didn't mention that action_batches also have action_batch_id, and if so. it will might be different from the batch_id from item table – Raffaello.D.Huke Feb 19 '16 at 02:00
Good point. I will wait for his comment regarding this then. The columns of the outer query should be visible in the subquery. – Tim Biegeleisen Feb 19 '16 at 02:00
`action_batch_id` will be changed for empty subquery result (0 rows). `coalesce` should to wrap subquery, not vice versa. – Abelisto Feb 19 '16 at 02:38
Is there anything inherently evil in setting the `action_batch_id` to the same value? – Tim Biegeleisen Feb 19 '16 at 02:40
@TimBiegeleisen added the structure – kreek Feb 19 '16 at 02:50
I could be attacking this wrong but the goal is to not assign a batch item to a batch that is full (over batch_count or batch_size) – kreek Feb 19 '16 at 02:54
The logic in your query is confusing to me. – Tim Biegeleisen Feb 19 '16 at 02:56

Have SQL query fail if subquery returns null

2 Answers2