CODEWITHSUNDEEP
sqloracle-database
bd528
bd528

Reputation: 886

Inserting a record where corresponding record does not exist

I have TBLISSUES containing the following data :-

ISSUEID QUOTEID ISSUE_TYPE  DATE_ADDED  DATE_RESOLVED   RESOLVED_BY RESOLVED
4312    989435  TPIUplift   14-Jun-18                               N
4316    989077  TPIUplift   14-Jun-18                               N
4333    989071  TPIUplift   14-Jun-18                               N
4324    988403  TPIUplift   14-Jun-18   14-Jun-18       JohnSmith   Y
4323    988402  TPIUplift   14-Jun-18                               N
4317    987964  TPIUplift   14-Jun-18                               N

and this script :-

INSERT INTO tblissues (
    quoteid,
    issue_type,
    date_added,
    resolved
)
    SELECT
        *
    FROM
        (
            SELECT
                q.quoteid,
                'TPIUplift',
                trunc(SYSDATE) newissuedate,
                'N'
            FROM
                tblquotesnew q
                LEFT JOIN tbltariffsnew t ON q.tariff_id = t.tariffid
            WHERE
                t.uplift = 0
                AND   q.sales_route = 'TPI'
                AND   q.quote_status = 'Accepted'
                AND   q.status <> 'Cancelled'
        ) x

The script looks for issues (which will always exist). I want a new record to be added, only if an existing resolved record does not exist.

I have tried using this in the where clause of the insert, but a row is still added

  WHERE
        NOT EXISTS (
            SELECT
                *
            FROM
                tblissues
            WHERE
                (x.quoteid = tblissues.quoteid
                AND   tblissues.issue_type = 'TPIUplift'
                AND   tblissues.resolved <> 'Y')

So using the sample data above, I would not want a row inserted for QUOTEID 988403, as it's RESOLVED value is Y.

Is this possible?

Upvotes: 0

Views: 44

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

I would not want a row inserted for QUOTEID 988403, as it's RESOLVED value is Y.

Considering this and the fact that your QUOTEID is different, but ISSUE_TYPE same for all records , Shouldn't you be simply using this?

  WHERE NOT EXISTS 
  ..
  ..
        (
          WHERE   tblissues.ISSUE_TYPE = x.ISSUE_TYPE
           AND    tblissues.resolved   = 'Y' )

If not, please update your question and add some more rows for the expected output and clearly explain what you want.

Upvotes: 1

Related Questions