UPDATE query with INNER JOIN - key violations 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?

 

[bonojunkie]

i'm an idiot. the set statement was backwards. it's working fine now.