Update query - getting rid of duplicates.

[bugsy]

Hey All

I have 2 tables - A and B

I am assigning trade ID from A to B, where they have several common fields.
Two conditions are needed.
a) Only update records where B doesn't have trade ID yet. (done)
b) Do not update records with a Trade ID , if such id is in a a Table B.

not sure how to do the second one; would making Trade ID - primary key help ?

here is the text --

UPDATE tbl_eblotter_feed INNER JOIN tbl_broker_1 ON (tbl_eblotter_feed.nm_brokers = tbl_broker_1.Broker_ID) AND (tbl_eblotter_feed.CUSIP = tbl_broker_1.CUSIP) AND (tbl_eblotter_feed.SPN = tbl_broker_1.spn_cparty) AND (tbl_eblotter_feed.dt_mat = tbl_broker_1.dt_mat) AND (tbl_eblotter_feed.amt_notional = tbl_broker_1.amt_notional) SET tbl_broker_1.M3_ID = [tbl_eblotter_Feed].[M3_Id], tbl_broker_1.Desk = [tbl_eblotter_Feed].[Desk]
WHERE (((tbl_broker_1.M3_ID) Is Null));

 

[boblarson]

where they have several common fields.

This would tend to indicate that your database is not normalized and it should be so unless table B is an archive, you should not have common fields except for ONE which would be an ID field to link them together.

And, to answer your initial question - your query just has to have the where clause say where your table B field IS NULL. That would not overwrite a field which has a value.

 

[bugsy]

Table is not archive.
They cointain similar date, from 2 different sources

Making table B's Trade Id (Indexed. no Duplicates) solved the problem. I realized it as soon as I poster the message.