Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Question Unanswered: Uplete (update/delete) in one statement

    Hi,
    I'm very new to PostgreSQL but I need to perform some (complex?) operations on PostgreSQL 9.4.5... The first was an UPSERT, whereby I was inserting new data (word occurrences) or updating occurrence counts, and which I adapted off the Internet as follows:
    Code:
    WITH newbatch (normalized, original, count) as ( VALUES
    ('room','room',1)
    ),
    upsert as (
      UPDATE normalized_forms m SET count = m.count + 1
        FROM newbatch nb WHERE m.original = nb.original
      RETURNING m.*
    )
    INSERT INTO normalized_forms (normalized, original, count)
      SELECT normalized, original, count FROM newbatch
      WHERE NOT EXISTS (
        SELECT 1 FROM upsert up
          WHERE up.original = newbatch.original
      );
    This works fine. Now I need to do the opposite, i.e. decrease occurrence counts and, when one reaches 0, delete the corresponding row. I'm getting the entries to delete/update from another table. I've gotten as far as decreasing counts, but it doesn't perform the last part of my statement which should take care of the deletion. Here's what I have so far:
    Code:
    WITH oldbatch (normalized, original, count) as ( 
      SELECT original, count from all_words 
      WHERE id_doc='xxx'
    ),
    uplete as (
      UPDATE normalized_forms m SET count = m.count - ob.count
        FROM oldbatch ob WHERE m.original = ob.original
    )
    DELETE FROM normalized_forms
      WHERE count = 0
    ;
    As I said, it needs to be a single statement, and I expect it could/should be very similar to the UPSERT I'm using. Can anyone help me add the last strokes so it does what it should?

    Thanks!

    David

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Something like this should work:

    Code:
    WITH oldbatch (normalized, original, count) as ( 
      SELECT original, count from all_words 
      WHERE id_doc='xxx'
    ),
    uplete as (
      UPDATE normalized_forms m 
         SET count = m.count - ob.count
      FROM oldbatch ob 
      WHERE m.original = ob.original
      RETURNING m.*
    )
    DELETE FROM normalized_forms
    WHERE pk_column IN (select pk_column from uplete where count = 0)
    ;
    Replace "pk_column" with the name of the primary key column in the table normalized_forms
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •