UPDATE TABLE1 from TABLE2 Help

[kilabru]

ORCALE 8i / SQLPLUS
I have two tables, One and Two

Table One:
Main table that consists of three columns A, B, C, & D.
Columns A & B will always have data while C & D do not.
After insert to One, a trigger executes and sends a copy of the row to another table called Two. Table Two will then be updated for Column C & D at a point down the road. This will happen for several records created within a days time.
After C & D are updated, I then want to update table oNE with the updated information. The updates will have to correspond to the values in Column A & B. I cannot just truncate and update table A due to record addition is an ongoing process.

I understand the method of cursors and updates as to changing the data with a Set = 'change', but how do I do something similar in order to update the corresponding rows with the column data? There will be multiple rows of data updated. An example would be great, if any one has an idea. I have read considerably on cursors and procedures, but cannot seem to implement it. I just do not get it.

Thanks,
John

 

[rcurva]

From the way I understand your requirement, you have two identical tables. On the first insert, the tables will be both populated with same data (columns A & B). But the second table will receive the updates later right?

Then after some time, you will apply back everything updated from table 2 to table 1 that corresponds to columns A & B, which I presumed to be unique.

Question: Why do you need to keep two tables for this? Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[kilabru]

Let me try to explain this as best I can.

I have a table called RFQLINE. When data is inserted / updated it triggers output to a table called RFQ_NEW. The RFQ_NEW table is a combination of RFQ and QUOTE tables. The RFQ_NEW table is exported to a text file and sent via email to a vendor for cost for quotes. The vendor imports text into their database and updates data. After completed, RFQ_NEW is sent back as RFQ_NEW2. RFQ_NEW2 is then imported into QUOTE table. This works great. However, for updates I have to continually write a new script for every update in order to prevent duplicate records. Therefore, I have come up with a possible scenario:

1 - Use update per record
A. Trigger after insert/update to QUOTE table
B. Run Query that will export records of QUOTE table
that do not have completed quotes and send to vendor
C. After receiving updated QUOTE, it is now called
QUOTE2, Run a script that will update the records
in QUOTE table that are the same record in QUOTE2.

It seems to me that with cursors one could look at a record in QUOTE2, then look at a the same record in QUOTE, then update column 5 of QUOTE per the value of column 5 in QUOTE2. Perhaps it is a select, if, or combination statement that will do this. However, I have tryed and cannot get it to work for a column value update. I have been able to do a similar process and update with a value via SET = 'VALUE'. Problem is that I want it to update QUOTE per the column value in QUOTE2 which varies from record to record. I have tried to include a FROM QUOTE2 table, but doesn't work.

I hope this makes since.

John
kilabru@yahoo.com