Question Insert from Select Statement

[VentureFree]

Okay, I'm extremely frustrated with this, especially since I know I'm probably just doing something really stupid.

Basically I've got two tables with nearly identical structures (simplified here for clarity...I hope)

CREATE TABLE MyTable1
(
    ID          NUMBER,
    STATUS_CODE NUMBER,
    CHANGED_ON  DATE
);

CREATE VIEW MyTable1Latest AS
SELECT ID, STATUS_CODE, MAX(CHANGED_ON) AS LATEST_CHANGE
FROM MyTable1 GROUP BY ID, STATUS_CODE;

CREATE TABLE MyTable2
(
    ID          NUMBER,
    STATUS_CODE NUMBER
);

MyTable1 is just a status indicator, with CHANGED_ON indicating when the status changed.
MyTable1Latest is just a view to let me see only the latest status.
MyTable2 is a temporary table that I'm populating with new status info. The idea is to insert the data from MyTable2 into MyTable1 along with the time ONLY if there is a difference in status between the two tables, or if there is a new ID that needs to be tracked.

I can select those differences rather easily

SELECT x.ID,x.STATUS_CODE,SYSDATE AS CHANGED_ON
FROM MyTable2 x
LEFT JOIN MyTable1Latest l
ON x.ID = l.ID
WHERE x.STATUS_CODE <> l.STATUS_CODE OR l.ID IS NULL;

However, trying to insert that data is turning out to be problematic.

INSERT INTO MyTable1
SELECT x.ID,x.STATUS_CODE,SYSDATE AS CHANGED_ON
FROM MyTable2 x
LEFT JOIN
MyTable1Latest l
ON x.ID = l.ID
WHERE x.STATUS_CODE <> l.STATUS_CODE
OR l.ID IS NULL;

This insert statement always inserts the entire MyTable2 table, even the rows that don't differ from MyTable1? I'm stuck in a stupidity loop where I know the answer must be simple and obvious, but I just can't seem to find it. I've searched Google and various Forums, but somehow I'm not finding how to make this work.

EDIT: Just in case it matters, I'm doing this on Oracle 11g

 

[ss7thirty]

SELECT using the keyword INTO and that should accomplish what you are trying to do there.

 

[cjard]

If your select is selecting the correct data you want, and only that data, then telling Oracle to insert that data cannot possibly be inserting anything other than the data you've selected?

If it is, you've got a bug in Oracle that needs reporting.. Can you give reproduction examples? i.e. give us some example data INSERT statements so we can run this in our Oracle too (youve done the create tables, now I need some inserts for inserting dummy data)


ss7thirty, your advice is for SQL server, which uses the SELECT (data) INTO (tablename)
When an oracle user wants to do this he INSERT INTO (tablename) SELECT (data),
SELECT (data) INTO (variables) is used in stored procedures

 

[VentureFree]

Oh wow, I forgot that I posted this. My original post was incomplete, and I think the stuff I left out was probably important. I did eventually get it to work by simply coding the SQL explicitly instead of using the view.

How about I just post the actual code that I have instead of the sanitized version that I posted before. I don't think there are any security issues with doing this. What follows are all of the elements that come in to play for this problem. Basically the view that I presented wasn't as simple as I originally implied. Rather the view used a table-valued function to get its data, which I suspect is what caused the problem. Here are the actual structures that I used:

-- This is the main table containing current and historical info about phones at specific seats in an office
CREATE TABLE "SEAT_DETAIL"
(
  "IP"          VARCHAR2(15 BYTE),
  "STATUS_CODE" NUMBER(*,0),
  "EXTENSION"   NUMBER(*,0),
  "MOS"         NUMBER(5,4),
  "PHONE"       VARCHAR2(15 BYTE),
  "MACADDRESS"  VARCHAR2(17 BYTE),
  "CHANGED_ON" DATE
);

-- This is populated with new info before the INSERT and TRUNCATEd afterward
-- It's not a temp table because it's populated via sqlldr from a CSV file
CREATE TABLE "SEAT_DETAIL_NEW"
(
  "IP"          VARCHAR2(15 BYTE),
  "STATUS_CODE" NUMBER(*,0),
  "EXTENSION"   NUMBER(*,0),
  "MOS"         NUMBER(5,4),
  "PHONE"       VARCHAR2(15 BYTE),
  "MACADDRESS"  VARCHAR2(17 BYTE)
);

-- This is a type reflecting the above data for use with a table valued function (see below)
CREATE OR REPLACE
TYPE SEAT_DETAIL_TYPE IS OBJECT
(
  "IP"          VARCHAR2(15 BYTE),
  "STATUS_CODE" NUMBER,
  "EXTENSION"   NUMBER,
  "MOS"         NUMBER(5,4),
  "PHONE"       VARCHAR2(15 BYTE),
  "MACADDRESS"  VARCHAR2(17 BYTE),
  "CHANGED_ON" DATE
);

-- This is the actual table data type for the table valued function
CREATE OR REPLACE
TYPE SEAT_DETAIL_TABLE_TYPE AS TABLE OF SEAT_DETAIL_TYPE;

-- This is a table valued function that gets seat details only up to the given time
CREATE OR REPLACE
FUNCTION SEAT_DETAIL_SNAPSHOT
(LATEST IN DATE)
RETURN SEAT_DETAIL_TABLE_TYPE
AS
V_SEAT_DETAIL_TABLE_TYPE SEAT_DETAIL_TABLE_TYPE;
BEGIN
  SELECT SEAT_DETAIL_TYPE(
    x.IP,
    x.STATUS_CODE,
    x.EXTENSION,
    x.MOS,
    x.PHONE,
    x.MACADDRESS,
    x.CHANGED_ON)
  BULK COLLECT INTO V_SEAT_DETAIL_TABLE_TYPE
  FROM
    (SELECT s.*
      FROM SEAT_DETAIL s
      INNER JOIN
      (SELECT
          IP,
          MAX(CHANGED_ON) AS CHANGED_ON
        FROM
          SEAT_DETAIL
        WHERE
          CHANGED_ON < SEAT_DETAIL_SNAPSHOT.LATEST
        GROUP BY
          IP
      ) x ON s.IP = x.IP AND s.CHANGED_ON = x.CHANGED_ON
    ) x;
  RETURN V_SEAT_DETAIL_TABLE_TYPE;
EXCEPTION
  WHEN OTHERS THEN
    V_SEAT_DETAIL_TABLE_TYPE.DELETE;
  RETURN V_SEAT_DETAIL_TABLE_TYPE;
END;

-- This is a view that gets only the latest data by calling the above function with SYSDATE
CREATE OR REPLACE FORCE VIEW "SEAT_DETAIL_LATEST" ("IP", "STATUS_CODE", "EXTENSION", "MOS", "PHONE", "MACADDRESS", "CHANGED_ON")
AS
SELECT "IP",
  "STATUS_CODE",
  "EXTENSION",
  "MOS",
  "PHONE",
  "MACADDRESS",
  "CHANGED_ON"
FROM TABLE(SEAT_DETAIL_SNAPSHOT(SYSDATE));

In the end I replaced the select portion of the insert with the actual full select statement represented by the view. That is to say, instead of doing something like this (from memory, so I'm sorry if there are errors in this):

--I'm only interested in changes to status or extension and adding any new phones
INSERT INTO SEAT_DETAIL ("IP", "STATUS_CODE", "EXTENSION", "MOS", "PHONE", "MACADDRESS", "CHANGED_ON")
SELECT n.IP,
    n.STATUS_CODE,
    n.EXTENSION,
    n.MOS,
    n.PHONE,
    n.MACADDRESS,
    SYSDATE AS CHANGED_ON
FROM SEAT_DETAIL_NEW n
LEFT JOIN
SEAT_DETAIL_LATEST l
ON n.IP = l.IP
WHERE COALESCE(n.STATUS_CODE, -1) <> COALESCE(l.STATUS_CODE, -1)
OR COALESCE(n.EXTENSION, -1) <> COALESCE(l.EXTENSION, -1)
OR l.IP IS NULL;

I ended up doing something like this (again from memory, but I think it's probably good enough to at least demonstrate what I was trying to do):

INSERT INTO SEAT_DETAIL ("IP", "STATUS_CODE", "EXTENSION", "MOS", "PHONE", "MACADDRESS", "CHANGED_ON")
SELECT n.IP,
    n.STATUS_CODE,
    n.EXTENSION,
    n.MOS,
    n.PHONE,
    n.MACADDRESS,
    SYSDATE AS CHANGED_ON
FROM SEAT_DETAIL_NEW n
LEFT JOIN
(SELECT s.*
      FROM SEAT_DETAIL s
      INNER JOIN
      (SELECT
          IP,
          MAX(CHANGED_ON) AS CHANGED_ON
        FROM
          SEAT_DETAIL
        GROUP BY
          IP
      ) x ON s.IP = x.IP AND s.CHANGED_ON = x.CHANGED_ON
    ) l
ON n.IP = l.IP
WHERE COALESCE(n.STATUS_CODE, -1) <> COALESCE(l.STATUS_CODE, -1)
OR COALESCE(n.EXTENSION, -1) <> COALESCE(l.EXTENSION, -1)
OR l.IP IS NULL;

This last query seemed to do it, though I'm not sure exactly why the previous one didn't. As I said, I'm guessing it has something to do with the fact that it utilizes a function to get its data.