update query - key violation error

[bonojunkie]

Hi, looking for insight on how to troubleshoot this update query.

I'm trying to update taxes.state_id with values from state.state_id where
state=state. I tested this SQL stmt by going to datasheet view in the query
and it appears to return the expected update data on all rows, but when i try
to run it i get this error:

"..didn't update.. 5436 records due to key violations..."

this is the actual query:

UPDATE state INNER JOIN taxes ON state.state=taxes.state
SET state.state_id = taxes.state_id
WHERE (state.state)=(taxes.state);

table field properties:

state.state = text (5)
taxes.state = text (5)
state.state_id = long int (indexed=Y, no dups)
taxes.state_id = long int (indexed=Y, dups ok)

table STATE has no primary key; table TAXES has tax_ID as pk.

In the query designer, the join relationship between state.state and taxes.
state is 'only include rows where joined fields are equal'.

I hope i provided enough background info.. i dont know what else to try to
resolve, it all looks like it should work to me. Any thoughts?

 

[Bob Barrows]

Hi, looking for insight on how to troubleshoot this update query.

I'm trying to update taxes.state_id with values from state.state_id
where state=state. I tested this SQL stmt by going to datasheet view
in the query and it appears to return the expected update data on all
rows, but when i try to run it i get this error:

"..didn't update.. 5436 records due to key violations..."

this is the actual query:

UPDATE state INNER JOIN taxes ON state.state=taxes.state
SET state.state_id = taxes.state_id

You said "I'm trying to update taxes.state_id with values from
state.state_id". Why did you attept to do the reverse in this query? Change
it to

SET taxes.state_id = state.state_id

In the query designer, the join relationship between state.state and
taxes. state is 'only include rows where joined fields are equal'.

Yes, that's what the words "INNER JOIN" mean

 

[bonojunkie]

for the love... d'ja ever have one of the DOH moments and feel like a total
HOMER??

so sorry, thank you Bob for pointing out the obvious IDIOCY on my part...

Bob Barrows wrote:
Change