title: Why PostgreSQL CTE with DELETE is not working?
body: <p>I was trying to delete a record from my stock table if the update in the same table results in quantity 0 using two CTEs. 
  The upserts are working, but the delete is not generating the result I was expecting. the quantity in stock table is changing to zero but the record is not being deleted.
  Table structure:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS stock_location (
      stock_location_id SERIAL
      , site_code VARCHAR(10) NOT NULL
      , location_code VARCHAR(50) NOT NULL
      , status CHAR(1) NOT NULL DEFAULT 'A'
      , CONSTRAINT pk_stock_location PRIMARY KEY (stock_location_id)
      , CONSTRAINT ui_stock_location__keys UNIQUE (site_code, location_code)
  );
  
  CREATE TABLE IF NOT EXISTS stock (
      stock_id SERIAL
      , stock_location_id INT NOT NULL
      , item_code VARCHAR(50) NOT NULL
      , quantity FLOAT NOT NULL
      , CONSTRAINT pk_stock PRIMARY KEY (stock_id)
      , CONSTRAINT ui_stock__keys UNIQUE (stock_location_id, item_code)
      , CONSTRAINT fk_stock__stock_location FOREIGN KEY (stock_location_id)
          REFERENCES stock_location (stock_location_id)
          ON DELETE CASCADE ON UPDATE CASCADE
  );
  </code></pre>
  
  <p>This is how the statement looks like:</p>
  
  <pre><code>WITH stock_location_upsert AS (
      INSERT INTO stock_location (
          site_code
          , location_code
          , status
      ) VALUES (
          inSiteCode
          , inLocationCode
          , inStatus
      )
      ON CONFLICT ON CONSTRAINT ui_stock_location__keys
          DO UPDATE SET
              status = inStatus
      RETURNING stock_location_id
  )
  , stock_upsert AS (
      INSERT INTO stock (
          stock_location_id
          , item_code
          , quantity
      )
      SELECT
          slo.stock_location_id
          , inItemCode
          , inQuantity
      FROM stock_location_upsert slo
      ON CONFLICT ON CONSTRAINT ui_stock__keys
          DO UPDATE SET
              quantity = stock.quantity + inQuantity
          RETURNING stock_id, quantity
  )
  DELETE FROM stock stk
  USING stock_upsert stk2
  WHERE stk.stock_id = stk2.stock_id
      AND stk.quantity = 0;
  </code></pre>
  
  <p>Does anyone know what's going on?</p>
  
  <p>This is an example of what I'm trying to do:</p>
  
  <pre><code>DROP TABLE IF EXISTS test1;
  
  CREATE TABLE IF  NOT EXISTS test1 (
      id serial
      , code VARCHAR(10) NOT NULL
      , description VARCHAR(100) NOT NULL
      , quantity INT NOT NULL
      , CONSTRAINT pk_test1 PRIMARY KEY (id)
      , CONSTRAINT ui_test1 UNIQUE (code)
  );
  
  -- UPSERT
  WITH test1_upsert AS (
      INSERT INTO test1 (
          code, description, quantity
      ) VALUES (
          '01', 'DESC 01', 1
      ) 
      ON CONFLICT ON CONSTRAINT ui_test1 
          DO UPDATE SET
              description = 'DESC 02'
              , quantity = 0
      RETURNING test1.id, test1.quantity
  )
  DELETE FROM test1 
  USING test1_upsert
  WHERE test1.id = test1_upsert.id
      AND test1_upsert.quantity = 0;
  </code></pre>
  
  <p>The second time the UPSERT command runs, it should delete the record from test1 once the quantity will be updated to zero. </p>
  
  <p>Makes sense?</p>