How can I use a UNION statement or an OR statement inside psql's UPDATE command?

Question

I have an app that vends a 'code' to users through an api. A code belongs to a pool of codes that when a user hits an endpoint, he/she will get a code from this 'pool'. At the moment there is only 1 'pool' of codes where a code can be vended. That idea is best expressed below in the following sql.

<<-SQL
    UPDATE codes SET vended_at = NOW()
      WHERE id = (
        SELECT "codes"."id"
        FROM "codes"
        INNER JOIN "code_batches" ON "code_batches"."id" = "codes"."code_batch_id"
        WHERE "codes"."vended_at" IS NULL
          AND "code_batches"."active" = true
        ORDER BY "code_batches"."end_at" ASC
        FOR UPDATE OF "codes" SKIP LOCKED
        LIMIT 1 
      )
    RETURNING *;
 SQL

So basically, when the end point is pinged, I am returning a code that is active and its vended_at field is NULL.

Now what I need to do is to build off of this sql so that a user can get a code from this pool or from a second pool. So for example, lets say that if the user couldn't get a code from this pool (we will call it A represented by the above sql), I need to vend a code from another pool (we will call it B).

I looked up the documentation of postgresql and I think what I want to do is to either 1). Use a UNION somehow to combine pools A and B into one megapool to vend a code or if I can't vend a code through pool A, use postgresql's OR clause to select from pool B.

The problem is that I can't seem to be able to use either of these syntaxes. I've tried something along the lines like this, tweaking it with different variations.

<<-SQL
    UPDATE codes SET vended_at = NOW()
      WHERE id = (
        SELECT "codes"."id"
        FROM "codes"
        INNER JOIN "code_batches" ON "code_batches"."id" = "codes"."code_batch_id"
        WHERE "codes"."vended_at" IS NULL
          AND "code_batches"."active" = true
        ORDER BY "code_batches"."end_at" ASC
        FOR UPDATE OF "codes" SKIP LOCKED
        LIMIT 1 
      ) UNION (
          ######## SELECT SOME OTHER STUFF #########
      )
    RETURNING *;
 SQL

or

<<-SQL
    UPDATE codes SET vended_at = NOW()
      WHERE id = (
        SELECT "codes"."id"
        FROM "codes"
        INNER JOIN "code_batches" ON "code_batches"."id" = "codes"."code_batch_id"
        WHERE "codes"."vended_at" IS NULL
          AND "code_batches"."active" = true
        ORDER BY "code_batches"."end_at" ASC
        FOR UPDATE OF "codes" SKIP LOCKED
        LIMIT 1 
      ) OR (
          ######## SELECT SOME OTHER STUFF USING OR #########
      )
    RETURNING *;
 SQL

So far the syntax is off and I'm starting to wonder if I can even use this approach for what I'm trying to do. I can't determine if my approach is wrong or if maybe I am using UNION, OR, and SUB-SELECTS wrong. Does anyone have any advice I can try to accomplish my goal? Thank you.

####### EDIT ########

To illustrate and make the concept even easier, I essentially want to do this.

<<-SQL
    UPDATE codes SET vended_at = NOW()
      WHERE id = (
        CRITERIA 1
      ) 
      OR/UNION 
      (
        CRITERIA 2
      )
    RETURNING *;
 SQL

Answer 1

Use one table to store both pools.

Add a pool_number column to the codes table to indicate which pool the code is in, then just add

ORDER BY pool_number

to your existing query.