Composite Primary Key - How to do DML based on Primary Key ? 1

[Friend33]

I have a Composite Primary Key (PK) based on 4 columns.
How to do DML (Insert, Update, Delete) based on the uniqueness of PK ?

here it goes in a bit detail

1) If the PK for the incoming record does not exist in the master table (database), create a new record.

Q: Can you please let me know asto how to implement this ?

2) If the PK for the incoming record exist in the master table (database), record is updated.

Q: How do we implement this ?

3) Lastly, if the master table contains records that are not part of the incoming records, these records are deleted from the master table.

Q: How do we put this in practice ?

Hope to you from you all.

Thanks and regards,

Friend33

 

[Dagon]

A composite key is exactly the same as a single column primary key, except that it consists of more than one column. Hence, if your table has a composite key of col1 and col2, you would update a row uniquely using:

update table
set column3=value_x
where column2 = value_y
and column1 = value_z

To accomplish tasks 1 and 2, I'd recommend you investigate the MERGE command.

You could accomplish 3 with a delete command e.g.

delete from master_table m
where not exists
(select 1 from incoming_table i
where i.key1 = m.key1
and i.key2 = m.key2)

 

[Turkbear]

Hi,
One approach:

For the Insert/Update process look at the MERGE command..

To use the incoming data as a test as to whether an existing record should be deleted, you could create a table for the incoming data and use it in a WHERE clause :

Delete from existing records_table where PK not in ( select PK from incomingdata_table)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Friend33]

Hi Dagon,

Thanks a million for your prompt reply !!

But, to put it simply, how do I check the condition for UPDATE ?

Condition: If the PK for the incoming record exist in the database, the corresponding master table (database) is updated

Thanks and regards,

Friend33

 

[Turkbear]

Hi,
That's what the MERGE command is designed to do ( If exists, UPDATE, if not, INSERT)

Read about it here:

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Friend33]

Hi Turkbear,

Can we write directly 'PK' as you have written as given

Delete from existing records_table where PK not in
(select PK from incomingdata_table)

OR, we have to write primary_key not in
OR, we have to write primary key.

would apprciate if you could let me know.

Thanks and regards,

Friend33

 

[Turkbear]

Hio,
No, you need to specify the exact field names for the composite key and set each's testing criteria...
I would recommend creating a new field in the table and update it with the concatenated field values used in the current composite PK.
Make this field your PK instead..You could use a trigger to maintain it once the existing table is updated initially.








To Paraphrase:"The Help you get is proportional to the Help you give.."