August 12, 2004 at 8:21 am
I'm programming an admin front-end for Bentley Digital Interplot, a system used to search archives for CAD files and retrieve them.
One of the fuctions it will perform is to take one set of drawings, and update all its fields with the data from ANOTHER set. The reason is that sometimes drawing sets are uploaded due to revisions, but none of the actual descriptive data has changed. Now the system has a table which stores the column names of each drawing set table, but I wanted to know if there's a way of updating all fields in a row with the same corresponding fields from another row, which use different primary keys (each drawing set has a unique identity number which is incremented every time a new drawing is uploaded). These rows will always be located in the same table, with the same column names, just different data in each field.
My other option is to use the field names stored in that other table, and just loop through with some massive update statements, but I want to know if there's a more elegant way of doing this.
Any ideas?
Thanks!
August 16, 2004 at 8:00 am
This was removed by the editor as SPAM
August 16, 2004 at 9:47 am
let me try to answer this:
say the table name is A
update t1
from A t1 join A t2 on (
t1.column1=t2.column1 and
t1.column2=t2.column2 and
......... and
t1.identity_column<t2.identity_column
)
The key point is you treat one physical table as 2 logical tables.
August 19, 2004 at 8:45 am
Since you don't want to overwrite the pkey (presumably letting system generate it) you will probably need to explicitly name the columns to update. Something like...
UPDATE t2 SET (col1 = t1.col1, col2 = t1.col2 ... ) FROM t1, t2 WHERE t1.matchingkey = t2.matchingkey
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply