Basic INSERT question for PL-SQL

Nostromo77 · #1 (**permalink**) December 16th, 2008, 04:00 PM

I have two tables, TABLE_OLD and TABLE_NEW, like so:

Code:

 
TABLE_OLD
PART_NUM Flag1 Flag2 Flag3
===============================
223 1 0 0
344 0 0 1
877 0 1 1
878 1 0 0
TABLE_NEW
PART_NO Flag1 Flag2 Flag3
===============================
223 NULL NULL NULL
344 NULL NULL NULL
877 NULL NULL NULL
878 NULL NULL NULL

I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty.
Most if teh INSERT examples I have seen so far deal with simple inserts of hard-coded data, not this sort of situation. Is the query I will want to run something like this?:

Code:

INSERT INTO 
TABLE_NEW.Flag1
TABLE_NEW.Flag2
TABLE_NEW.Flag3
FROM 
TABLE_OLD.Flag1
TABLE_OLD.Flag2
TABLE_OLD.Flag3
WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO;

Thanks for any help.

dparsons · #2 (**permalink**) December 16th, 2008, 05:03 PM

Hmm. You don't want an insert you want an update (based upon your explanation you have said that the PartNo column in Table_New is already populated, you just need the corresponding flags from Table_Old)

In T-SQL the solution looks like this (Sorry I only know rudimentary PL-SQL but, i am imagining, the solution in Oracle would be similar):

sql Code:

UPDATE tnew
SET tnew.Flag1 = told.Flag1,
tnew.Flag2 = told.Flag2,
tnew.Flag3 = told.Flag3
FROM Table_New tnew
INNER JOIN Table_Old told ON tnew.Part_Num = told.Part_Num

If you really want an insert, something like this should work:

sql Code:

INSERT INTO Table_New (Part_Num, Flag1, Flag2, Flag3)
SELECT Part_Num, Flag1, Flag2, Flag3 FROM Table_Old;

The insert statement assumes that you truncate Table_New and insert the Part_Num from Table_Old into Table_New.

hth.
-Doug

Nostromo77 · #3 (**permalink**) December 16th, 2008, 07:06 PM

Thanks very much for the advice; you were right, I did indeed want an UPDATE instead of an INSERT. Since the source table was infested with duplicate part numbers, the following query from a helpful person at Code Project weeded them out:

Code:

UPDATE WEB_PRODUCT_TEST_COPY p
SET ( 
CONTAINS_MARBLE_FLG, 
IS_MARBLE_FLG, 
CONTAINS_SMALL_BALL_FLG, 
IS_SMALL_BALL_FLG, 
SMALL_PARTS_FLG, 
BALLOON_FLG 
)
=(SELECT 
CONTAINS_MARBLE_FLG, 
IS_MARBLE_FLG, 
CONTAINS_SMALL_BALL_FLG, 
IS_SMALL_BALL_FLG, 
SMALL_PARTS_FLG, 
BALLOON_FLG
FROM TEST_CPSC_BOOL a1
WHERE PART_NUM = p.PART_NO
AND NOT EXISTS (SELECT 1
FROM TEST_CPSC_BOOL a2
WHERE a1.ROWID < a2.ROWID
AND a1.PART_NUM = a2.PART_NUM));

dparsons · #4 (**permalink**) December 16th, 2008, 08:07 PM

Glad it worked out for you! ^^

Need to download code?

Navigation