Update query

[Amerigo]

Got the following update query -




It updates ID value in table 1 when there is a full match in table 2.
Problem: There are occasionally 2 full matches, and the ID of the first one get's assigned.
I cannot put index, since some vallues are to remain blank.

any suggestions ?

 
UPDATE 
tblCONCORD INNER JOIN tblBBG ON
 (tblBBG.BBG_ISIN=tblCONCORD.CONCORD_ISIN)  AND 
(tblBBG.BBG_Price=tblCONCORD.Price) AND
 (tblBBG.BBG_Amount=tblCONCORD.Concord_Amount) AND 
(tblBBG.[Buy/Sell]<> tblCONCORD.Direction) 
 SET tblBBG.BBG_TradeID = tblCONCORD.Trade_ID WHERE BBG_TradeID =''

[Amerigo]

so i tried to do this with making the first "grouped query" with the undesired record thus disappearing, but this way i cannot update getting "you must use updatable queries"

[orange]

You will have to identify the records to be updated in some manner.
How do you identify the records that "must" remain blank?

[Amerigo]

You will have to identify the records to be updated in some manner.
How do you identify the records that "must" remain blank?

Hey Orange
The unmatched records (i.e. the ones that didn't get matched by 4 criterias should remain blank)

it's bad that (group by) query can't be updated..; that would solve the problems

[orange]

Allen Browne discusses subqueries at
http://allenbrowne.com/subquery-01.html

You might find something there. As in his Delete Unmatched Records you may be able to use a subquery to exclude those records from the update. It might just make your query updateable??? Not sure.

[Amerigo]

Allen Browne discusses subqueries at
http://allenbrowne.com/subquery-01.html

You might find something there. As in his Delete Unmatched Records you may be able to use a subquery to exclude those records from the update. It might just make your query updateable??? Not sure.

Hey Orange. I considered using subqueries; but then i need to delete a just 1 value; not the the second one - meaning

 
 
UPDATE tblBBG SET tblBBG.BBG_TradeID = ""
WHERE (((tblBBG.BBG_TradeID)="J320897"));

(the value offcourse will be added via VBA) from which i intend to run the query; BUT - I need to update just one instance; and not the other one(s)

Is there a way to combine SELECT TOP 1
with UPDATE ?

[orange]

Did you read Allen Browne's Delete Duplicate Records?

This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)

[Amerigo]

tx Orange
that example is deleting the extra records; so with slight modification it can be used for selecting just 1 out of duplicate records.

What i need is not to delete any, but when update is happening (by assigning ID to fields matching by value), not to assign the same value twice

don't see how it can work here, but perhaps i am just missing how.

i am thinking maybe it's easier, to populate, and then to delete extra record ?

so Allen Browne's suggestion led me to the following, which i think should work (will test with several duplicates)

 
UPDATE tblBBG SET BBG_TradeID = ""
WHERE tblBBG.[Ticket Number] = (SELECT Min(tblBBG.[Ticket Number]) AS [MinOfTicket Number]
FROM tblBBG
WHERE (((tblBBG.BBG_TradeID) In (SELECT [BBG_TradeID] FROM [tblBBG] As Tmp GROUP BY [BBG_TradeID] HAVING Count(*)>1 ) And (tblBBG.BBG_TradeID)<>"")));

[orange]

Sounds good. Test it with some test data to make sure it does what you want.
Allen Browne's sample was just to identify individual records where they were basically duplicates. Instead of updating both, updates only one based on subquery.

[Amerigo]

Sounds good. Test it with some test data to make sure it does what you want.
Allen Browne's sample was just to identify individual records where they were basically duplicates. Instead of updating both, updates only one based on subquery.

thanks Orange
i got the idea from Allen how to select just one record based on minimum value
works fine