Why does my update query swap values instead of updating only

[Guest]

I have two tables as follows:

workorderentity1
workorder,entityuid,entitytype,feature_id,feature_type...

and

Fbuilding1
objectid,facilityid...

I have setup the following update query.

UPDATE a_WORKORDERENTITY1 AS WE INNER JOIN sde_FBUILDING1 AS FB ON
WE.ENTITYUID = FB.FacilityID SET WE.FEATURE_ID = FB.OBJECTID
WHERE (((WE.ENTITYTYPE)="fbuilding") AND ((FB.OBJECTID)<>[WE].[FEATURE_ID]));

I expected that this query would copy the objectid value from sde_fbuilding1
to the feature_id field of the a_workorderentity1 table. However, what is
happening is the values are being swapped. The feature_id value is being
copied to the objectid field and the objectid value is being copied to the
feature_id field. Thus, each time I run the query it finds the same number
of mismatched records and reverses the field values.

This has happened in both a 2003 and 2007 database.

Any ideas why this is happening?

 

[Michel Walsh]

With Jet? That is most unlikely, a swapping case would be news to me. I
suspect there is no swap, BUT that the logic may lead you to think there is
some (due to a strange combination of records not updated because of the
inner join, and records effectively updated). It is also possible you have
multiple update of the same record:


--[pseudo code--
UPDATE table, iotas
SET table.f1=iotas.iota

each row of table will see its field f1 updated multiple time, but
definitively, it will keep only the LAST update it sees.



In any cases, if you don't see the problem, can you email me a reduced
version of your db, without sensitive data, that illustrates the problem?
vanderghast at msn. Dot com.



Hoping it may help
Vanderghast, Access MVP

 

[Guest]

Michel, thanks for responding to my posting. I've read similar postings
before and thought why are they wasting our time instead of reading the
manual or researching the issue. Well, this turns out to be a problem with
the data.

It turns out that the data in question had multiple records with identical
values in the link field. That's why the swapping was going on. It was
doing exactly as you explained below.

Thanks again for taking the time to read my posting and responding to it.

With Jet? That is most unlikely, a swapping case would be news to me. I
suspect there is no swap, BUT that the logic may lead you to think there is
some (due to a strange combination of records not updated because of the
inner join, and records effectively updated). It is also possible you have
multiple update of the same record:


--[pseudo code--
UPDATE table, iotas
SET table.f1=iotas.iota

each row of table will see its field f1 updated multiple time, but
definitively, it will keep only the LAST update it sees.



In any cases, if you don't see the problem, can you email me a reduced
version of your db, without sensitive data, that illustrates the problem?
vanderghast at msn. Dot com.



Hoping it may help
Vanderghast, Access MVP

I have two tables as follows:

workorderentity1
workorder,entityuid,entitytype,feature_id,feature_type...

and

Fbuilding1
objectid,facilityid...

I have setup the following update query.

UPDATE a_WORKORDERENTITY1 AS WE INNER JOIN sde_FBUILDING1 AS FB ON
WE.ENTITYUID = FB.FacilityID SET WE.FEATURE_ID = FB.OBJECTID
WHERE (((WE.ENTITYTYPE)="fbuilding") AND
((FB.OBJECTID)<>[WE].[FEATURE_ID]));

I expected that this query would copy the objectid value from
sde_fbuilding1
to the feature_id field of the a_workorderentity1 table. However, what is
happening is the values are being swapped. The feature_id value is being
copied to the objectid field and the objectid value is being copied to the
feature_id field. Thus, each time I run the query it finds the same
number
of mismatched records and reverses the field values.

This has happened in both a 2003 and 2007 database.

Any ideas why this is happening?