Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2008, 04:00 PM
Authorized User
Points: 195, Level: 3
Points: 195, Level: 3 Points: 195, Level: 3 Points: 195, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
Default Basic INSERT question for PL-SQL

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.
 
Old December 16th, 2008, 05:03 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
The Following User Says Thank You to dparsons For This Useful Post:
Nostromo77 (December 16th, 2008)
 
Old December 16th, 2008, 07:06 PM
Authorized User
Points: 195, Level: 3
Points: 195, Level: 3 Points: 195, Level: 3 Points: 195, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , , .
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
Default

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));

Last edited by Nostromo77; December 16th, 2008 at 07:08 PM..
 
Old December 16th, 2008, 08:07 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Glad it worked out for you! ^^
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================




Similar Threads
Thread Thread Starter Forum Replies Last Post
PL/SQL cooldude87801 Oracle 1 March 3rd, 2005 10:54 AM
PL/SQL i need help loveboy23 Oracle 3 January 6th, 2005 01:14 AM
PL/SQL help carly_1 Oracle 1 October 19th, 2003 08:14 PM
A Basic SQL Server Question shaked21 SQL Server 2000 9 October 8th, 2003 02:00 AM
PL/SQL help Blue Oracle 0 August 12th, 2003 02:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.