SQL query Exists AND NOT Exists

Question

I have a table where I need to insert the data from a view which has 2 conditions : 1) Insert those data in a table where pk is unavailable in a transaction table but is available in the view 2) if pk is there but data is different from the view then insert those data also

        Insert into table A
    a,
    b,
    c,
    d
SELECT 
a,
b,
c,
d
from View sa
WHERE not Exists 
(Select * FROM table A q
    where SA.a = q.a )
    OR 
    CASE WHEN  Exists
    (Select * FROM table A q
    where SA.a = q.a 
    AND 


                        SA.b <> q.b
                        OR SA.c <> q.c
                        OR SA.d <> q.d
                    )

Any help appreciated!

The syntax on the `case` expression is wrong. Actually remove `case when` and it probably works. — shawnt00, Mar 08 '16 at 01:21
How can you insert primary keys that already exist? Perhaps sample data and expected results would be helpful... — sgeddes, Mar 08 '16 at 01:35

Hart CO · Answer 1 · 2016-03-08T02:10:27.757

1

I believe this is the proper format for your INSERT with SELECT for Netezza. I removed the invalid CASE expression and there should be no other syntax errors, but not sure about the logic:

Insert into tableA (a,b,c,d) (
SELECT a,b,c,d
from View sa
WHERE not Exists 
   (Select * FROM tableA q
    where SA.a = q.a 
    )
 OR Exists
    (Select * FROM tableA q2
    where SA.a = q2.a 
    AND SA.b <> q2.b
     OR SA.c <> q2.c
     OR SA.d <> q2.d
    )
)

Edit: I think it may have been complaining about the re-use of the q table alias.

edited Mar 08 '16 at 02:10

answered Mar 08 '16 at 01:57

Hart CO

34,064
6
48
63

ERROR [HY000] ERROR: (2) This form of correlated query is not supported - consider rewriting – mehtat_90 Mar 08 '16 at 02:00
That seems odd, does the `SELECT` portion run without the insert when you leave off the `OR EXISTS` section? If so, perhaps it doesn't allow re-use of the table alias, and the `OR EXISTS` would need to be something other than `q`. Just shots in the dark, don't know Netezza. – Hart CO Mar 08 '16 at 02:06
Yes. It is only when I run the second condition, the issue arises – mehtat_90 Mar 08 '16 at 02:07
Try with a different alias in the 2nd portion. – Hart CO Mar 08 '16 at 02:10

score 1 · Answer 2 · answered Mar 08 '16 at 03:31

As a general rule, correlated subqueries are not for Netezza. Obviously you can use them in many cases, but often at the cost of turning an MPP platform into a serial processor. Bad.

You can rewrite your insert a number of ways, but this seems the clearest to me. I have no idea what this logic is trying to do, but I replicated it nonetheless. You may have an issue if view contains duplicates; this can be addressed with a little more knowledge of your data.

insert into tableA (
  a
  ,b
  ,c
  ,d
)
select
  viw.a
  ,viw.b
  ,viw.c
  ,viw.d
from
  view viw
  join tableA tba on
    (viw.a = tba.a)
    or (
      viw.a = tba.a
      and viw.b <> tba.b
      or viw.c <> tba.c
      or viw.d <> tba.d
    )

You could remove the possibility of view duplicates by inserting into tableA from an actual table and use the rowids found there. Perhaps something like this:

create temporary table temp_view as
  select * from view
distribute on (some_key);

Then collect rowids to insert like so:

insert into tableA (
  a
  ,b
  ,c
  ,d
)
with mar as ( --Match 'a' records.
  select
    viw.rowid insert_rowid
  from
    temp_view viw
    join tableA tba using (a)
), mnb as ( --Match against 'b'
  select
    viw.rowid
  from
    temp_view viw
    join tableA tba on 
      viw.a = tba.a
      and viw.b <> tba.b
      or viw.c <> tba.c
      or viw.d <> tba.d
), rws as ( --All rowids.
  select * from mar
  union select * from mnb
)
select
  a
  ,b
  ,c
  ,d
from
  temp_view viw
  join rws on rws.insert_rowid = viw.rowid;

SQL query Exists AND NOT Exists

2 Answers2