Update query that is updating fields not included in any statement

T

Todd

I have been downloading data from a website that I import into a table. From
there I run queries on the data to append and update my tracking table for
the data I download from the internet. After running an update query for
just one field in the table (joined to the downloaded data) I have noticed
that other fields are being updated. I checked the SQL View and cannot
diagnose how the query is updating other fields. I did change the Dynaset in
some queries but how could that change fields not even chosen in the design
view or the SQL view? I have a feeling it has to do with the Dynaset
settings. Any ideas would be greatly appreciated. Thanks.
 
T

Todd

This is the first query I run. It appends any records from my import table
that do not match the tracking table (* Base) joined on the tracking number.

INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN],
[Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion
Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section],
Product )
SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN
Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import
fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import
fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed],
[qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import
fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed],
[qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD
Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog
Import fixed].[Product Fixed]
FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #]
WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null));

This is the second query I run to look for any asr notes that are different
between the base and import tables.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR
Notes]
WHERE ((([07517 m_eggers Base].[ASR Notes])<>[qry SalesLog Import
fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry
SalesLog Import fixed].[ASR Notes]) Is Not Null));

I run this query to put the BTN into the base table if the import table has
a BTN in the field.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed]
WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import
fixed].[BTN Fixed]) Is Not Null));

What is in here that would update some of the other fields in my base table
with information from by import table?
 
J

Jerry Whittle

The SET statement seems to limit things to the [07517 m_eggers Base].BTN and
[07517 m_eggers Base].[ASR Notes] fields. However you have an inner join
between two tables in the UPDATE section. I'm wondering if this is causing an
wildcard-like condition.

Instead of the inner join, I would use an IN or EXISTS statement in the
WHERE clause. I also wouldn't worry about the <> or Not Null in the Where
clause. If they happen to be equal, it won't hurt to update them. Depending
on the business rules, you might need to avoid updating a current value with
a Null so this something like this part might need to stay.

[SalesLog Import fixed].[ASR Notes]) Is Not Null

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Todd said:
This is the first query I run. It appends any records from my import table
that do not match the tracking table (* Base) joined on the tracking number.

INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN],
[Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion
Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section],
Product )
SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN
Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import
fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import
fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed],
[qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import
fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed],
[qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD
Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog
Import fixed].[Product Fixed]
FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #]
WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null));

This is the second query I run to look for any asr notes that are different
between the base and import tables.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR
Notes]
WHERE ((([07517 m_eggers Base].[ASR Notes])<>[qry SalesLog Import
fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry
SalesLog Import fixed].[ASR Notes]) Is Not Null));

I run this query to put the BTN into the base table if the import table has
a BTN in the field.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed]
WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import
fixed].[BTN Fixed]) Is Not Null));

What is in here that would update some of the other fields in my base table
with information from by import table?

Jerry Whittle said:
Post the SQL here.
Click to expand...
Click to expand...
 
T

Todd

Thank you for your response. I realized afterword the field that was
updating (*base.status) was included in the append query. Either I was
tweaking the queries and accidentally included it (you know, double click the
table and drag all fields into the design view) or it was something I/someone
else did. I now believe this may have been the problem. Your suggestion
that the inner join is causing a wildcard like condition is interesting. I
was thinking similarly with my dynaset line of thought. That may still be
the case. I am still too ignorant about the workings of a database to
completely understand.

I will have to look into the IN and EXISTS statements to learn about them.

I was wondering if anyone would catch the <> and Not Null part. It is
important for me to not update my tracking database with a value to null and
the <> part helps me to investigate/think about the how and why my import
data is the way it is. It also allows me to see useful data, like the
submitted date (to acquire an idea of how far back these records are
changing) and exactly what records will be changed, by first running it as a
selest query (without having thousands of records return).

Again, thanks for your time.
cheap.fast.good. another twist on the old adage you can't have it all.

Jerry Whittle said:
The SET statement seems to limit things to the [07517 m_eggers Base].BTN and
[07517 m_eggers Base].[ASR Notes] fields. However you have an inner join
between two tables in the UPDATE section. I'm wondering if this is causing an
wildcard-like condition.

Instead of the inner join, I would use an IN or EXISTS statement in the
WHERE clause. I also wouldn't worry about the <> or Not Null in the Where
clause. If they happen to be equal, it won't hurt to update them. Depending
on the business rules, you might need to avoid updating a current value with
a Null so this something like this part might need to stay.

[SalesLog Import fixed].[ASR Notes]) Is Not Null

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Todd said:
This is the first query I run. It appends any records from my import table
that do not match the tracking table (* Base) joined on the tracking number.

INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN],
[Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion
Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section],
Product )
SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN
Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import
fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import
fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed],
[qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import
fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed],
[qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD
Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog
Import fixed].[Product Fixed]
FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #]
WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null));

This is the second query I run to look for any asr notes that are different
between the base and import tables.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR
Notes]
WHERE ((([07517 m_eggers Base].[ASR Notes])<>[qry SalesLog Import
fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry
SalesLog Import fixed].[ASR Notes]) Is Not Null));

I run this query to put the BTN into the base table if the import table has
a BTN in the field.

UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry
SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking
#] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed]
WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import
fixed].[BTN Fixed]) Is Not Null));

What is in here that would update some of the other fields in my base table
with information from by import table?

Jerry Whittle said:
Post the SQL here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have been downloading data from a website that I import into a table. From
there I run queries on the data to append and update my tracking table for
the data I download from the internet. After running an update query for
just one field in the table (joined to the downloaded data) I have noticed
that other fields are being updated. I checked the SQL View and cannot
diagnose how the query is updating other fields. I did change the Dynaset in
some queries but how could that change fields not even chosen in the design
view or the SQL view? I have a feeling it has to do with the Dynaset
settings. Any ideas would be greatly appreciated. Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update joined tables with info that is currently in Excel? 5
Update Query (multiple fields) 11
Query not updateable, yet in form it is. 1
Change an update query 1
If statement in Update query 7
Use Update Query to Add Fields 1
Update Query not posting 2
update query w some fields null 1

Top