Update or Append query help

#1 (**permalink**) 28-Jul-2006, 08:33 AM

This is probably obvious but I am a bit 'rusty' with my SQL and so I am
missing it.

I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
Inventory is branch, date, and inventory levels of various products
(structure needed for join with another table). DAILY_INV2 is an intermediary
conversion table and is branch, date, product id, quantity and storage
location.

I am trying to set the quantity for a particular product in the Branch Daily
Inventory based on the quantity value from the matching row in the DAILY_INV2
table. Here is my SQL...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13037-update-or-append-query-help.html

UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP Plant
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
[DAILY_INV2].Quantity
WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
Location])="1"));

The test run lists values of all zeros which is not the correct result.

Any insight?

#2 (**permalink**) 28-Jul-2006, 08:33 AM

"B Miller" wrote in message
news

9AE0E58-C6C7-4AA9-9330-6C61652A5518@microsoft.com...
> This is probably obvious but I am a bit 'rusty' with my SQL and so I am
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
> missing it.
>
> I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
> Inventory is branch, date, and inventory levels of various products
> (structure needed for join with another table). DAILY_INV2 is an
intermediary
> conversion table and is branch, date, product id, quantity and storage
> location.
>
> I am trying to set the quantity for a particular product in the Branch
Daily
> Inventory based on the quantity value from the matching row in the
DAILY_INV2
> table. Here is my SQL...
>
> UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
> Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP
Plant
> ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
> [DAILY_INV2].Quantity
> WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
> Location])="1"));
>
> The test run lists values of all zeros which is not the correct result.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
>
> Any insight?

The Date column might be a datetime data type. If so, it is unlikely the two
would have the same value thus they are not practical in the JOIN. If that
is the case try removing the Date columns from the JOIN and move them to the
WHERE clause, testing for e.g.

[Branch Daily Inventory].Date > #07/06/2006# AND [Branch Daily
Inventory].Date < #07/08/2006# AND DAILY_INV2.Date > #07/06/2006# AND
DAILY_INV2.Date < #07/08/2006#

to get records for 07/07/2006

#3 (**permalink**) 28-Jul-2006, 08:33 AM

When I run the query as a 'select' I get records with values. But when I
test the update, I get the right number of rows but the column is 0. Silly
question, but does the update query show the results after the update or
before?

"Ron Hinds" wrote:

> "B Miller" wrote in message
> news

9AE0E58-C6C7-4AA9-9330-6C61652A5518@microsoft.com...
> > This is probably obvious but I am a bit 'rusty' with my SQL and so I am
> > missing it.
> >
> > I have 2 tables: Branch Daily Inventory and DAILY_INV2. Branch Daily
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
> > Inventory is branch, date, and inventory levels of various products
> > (structure needed for join with another table). DAILY_INV2 is an
> intermediary
> > conversion table and is branch, date, product id, quantity and storage
> > location.
> >
> > I am trying to set the quantity for a particular product in the Branch
> Daily
> > Inventory based on the quantity value from the matching row in the
> DAILY_INV2
> > table. Here is my SQL...
> >
> > UPDATE [Branch Daily Inventory] INNER JOIN DAILY_INV2 ON ([Branch Daily
> > Inventory].Date = DAILY_INV2.Date) AND ([Branch Daily Inventory].[SAP
> Plant
> > ID] = DAILY_INV2.[SAP Plant ID]) SET [Branch Daily Inventory].[3360I] =
> > [DAILY_INV2].Quantity
> > WHERE (((DAILY_INV2.[Part Number])=3360) AND ((DAILY_INV2.[Storage
> > Location])="1"));
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
> >
> > The test run lists values of all zeros which is not the correct result.
> >
> > Any insight?
>
> The Date column might be a datetime data type. If so, it is unlikely the two
> would have the same value thus they are not practical in the JOIN. If that
> is the case try removing the Date columns from the JOIN and move them to the
> WHERE clause, testing for e.g.
>
> [Branch Daily Inventory].Date > #07/06/2006# AND [Branch Daily
> Inventory].Date < #07/08/2006# AND DAILY_INV2.Date > #07/06/2006# AND
> DAILY_INV2.Date < #07/08/2006#
>
> to get records for 07/07/2006
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:34 AM

When running an update query in test mode using data from another table, it
does not show the real results. When I ran the query in 'real' mode, for a
single row, it correctly updated the targeted field with the correct value;
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13037
in test mode, the targeted field remained at zero - the non-updated value.
Very confusing.