Update status for dup records 1

[mansii]

Been trying the Search tool. Great posts there but cannot adjust it to fulfill my need.

Table: MyTable
Keys: Key1, Key2
Additional Condition Keys : Con1, Con2
Field to update: StatusKey

I want to UPDATE StatusKey to 'X', where Keys plus Con1 plus Con2 cannot be duplicate. Something like:

Key1  Key2   Con1   Con2   StatusKey
----  ----   ----   ----   ---------
A01   101    1             C
A01   101           1      C
A01   101                  C
A01   102    1             X
A01   102    1             X
A01   102           1      C

TIA,
mansii

 

[mp9]

Try this:

UPDATE MyTableSET StatusKey = "X"
WHERE Key1 In 
(SELECT Key1 FROM MyTable As Tmp 
GROUP BY Key1, Key2, Con1, Con2 
HAVING Count(*)>1  And Nz(Key2, "") = Nz(MyTable.Key2, "") And Nz(Con1, "") = Nz(MyTable.Con1, "") And Nz(Con2, "") = Nz(MyTable.Con2, ""));

http://www22.brinkster.com/accessory/

 

[mansii]

Pretty close!

But it says

Undefined function 'Nz' in expression

Sorry mp9, I'm not using VBA here. Just trying to write a regular SQL string, if it makes any difference.
I missed anything?

Anyway, tx for your prompt reply.

mansii

 

[PHV]

And what about this ?
UPDATE MyTable
SET StatusKey = "X"
WHERE Key1 & ',' & Key2 & ',' & Con1 & ',' & Con2 In
(SELECT Key1 & ',' & Key2 & ',' & Con1 & ',' & Con2
FROM MyTable GROUP BY Key1, Key2, Con1, Con2
HAVING Count(*)>1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Well you can try it without the Nz function but you still have to allow for Nulls being duplicated, so try this:

UPDATE MyTableSET StatusKey = "X"
WHERE Key1 In 
(SELECT Key1 FROM MyTable As Tmp 
GROUP BY Key1, Key2, Con1, Con2 
HAVING Count(*)>1  And (Key2 & "") = (MyTable.Key2 & "") And Con1 & "") = (MyTable.Con1 & "") And (Con2 & "") = (MyTable.Con2 & ""));

http://www22.brinkster.com/accessory/

 

[mansii]

Perfect!
But I had to modify it:

UPDATE MyTable
SET StatusKey = "X"
WHERE Key1 & ',' & Key2 & ',' & Con1 & ',' & Con2 In
(SELECT Key1 & ',' & Key2 & ',' & Con1 & ',' & Con2
 FROM MyTable GROUP BY Key1 & ',' & Key2 & ',' & Con1 & ',' & Con2
 HAVING Count(*)>1)

Tx.
mansii