VentureFree
Well-known member
- Joined
- Jan 9, 2008
- Messages
- 54
- Programming Experience
- 5-10
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)
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
However, trying to insert that data is turning out to be problematic.
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
Basically I've got two tables with nearly identical structures (simplified here for clarity...I hope)
VB.NET:
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
VB.NET:
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;
VB.NET:
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;
EDIT: Just in case it matters, I'm doing this on Oracle 11g
Last edited: