Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Update or Append query help

Discussion in 'Information Technology' started by B Miller, Jul 28, 2006.

  1. B Miller

    B Miller
    Expand Collapse
    Guest

    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...

    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.

    Any insight?
     
  2. Loading...


  3. Ron Hinds

    Ron Hinds
    Expand Collapse
    Guest

    "B Miller" <BMiller@discussions.microsoft.com> wrote in message
    news:D9AE0E58-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
    > 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.
    >
    > 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. B Miller

    B Miller
    Expand Collapse
    Guest

    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" <BMiller@discussions.microsoft.com> wrote in message
    > news:D9AE0E58-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
    > > 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.
    > >
    > > 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
    >
    >
    >
     
  5. B Miller

    B Miller
    Expand Collapse
    Guest

    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;
    in test mode, the targeted field remained at zero - the non-updated value.
    Very confusing.
     

Share This Page