Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Update all null data in a table where columns match

    Hi i have posted this somewhere else but have had little response so trying to broaden my help request.


    I have a small problem where i have 2 seperate tables

    Table 1 (tstockout)
    Table 2 (tRemoved)

    In Table 1

    Field Names: Date, Time, PartNo, Qty, Phase, Line, Machine

    There could be 10 items in the table.


    In Table 2

    Field Names: Phase, Line, Machine

    There will only be 1 row of data in this table


    At present the Phase, Line, Machine are not used in Table 1, however i would like to change this to log whatever data is stored in Table 2's fields.

    Example

    Table 1

    Current

    Date Time Part Qty Phase Line Machine
    Record 1...1/6/10, 14:30, 1234, 4, Null, Null, Null
    Record 2...1/6/10, 14:35, 2234, 1, Null, Null, Null
    Record 3...1/6/10, 14:38, 1334, 2, Null, Null, Null
    Record 4...1/6/10, 14:39, 1134, 1, Null, Null, Null

    Table 2
    Phase Line Machine
    Record 1... Phase 1, Line 1, Machine 1


    I need

    Record 1...1/6/10, 14:30, 1234, 4, Phase 1, Line 1, Machine 1
    Record 2...1/6/10, 14:35, 2234, 1, Phase 1, Line 1, Machine 1
    Record 3...1/6/10, 14:38, 1334, 2, Phase 1, Line 1, Machine 1
    Record 4...1/6/10, 14:39, 1134, 1, Phase 1, Line 1, Machine 1

    I understand that there is different ways to set the system up, however i am just trying to get this task done ASAP.



    I guess i need to use an update query but have no idea how to get it to do what i want.

    Please help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Based on the little you have said it is not simply that this could be set up "differently" but that it is set up wrong. It is also important to understand that in a relational database there is no such thing as "record 1". "record 2", "record 3" etc - rows do not have ordinal positions.

    However - you ask for an update statement that will work.
    Assuming that your data is an accurate representation of your problem this will work:
    Code:
    UPDATE [table 1], [table 2] SET [table 1].[Phase] =  [table 2].[Phase][table 1].[Line] =  [table 2].[Line], [table 1].[Machine] =  [table 2].[Machine]
    Run this only if you are happy with it and only on test data. It takes no account of nulls since there are no non-nulls in your data. It does not "update data where columns match" since there are no columns in the sample data to match.

  3. #3
    Join Date
    Mar 2003
    Posts
    225
    ok thanks for your help, I have attached a trial of this, but it doesn't seem to update as i need it to.

    I have ammended the code above to match my table structure..

    But it still not quite there.

    Please help
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    When you changed the query you swapped round the table names. Why did you do that? You are now updating the wrong table.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've had a look at your cross post here: UtterAccess Discussion Forums > update table based on contents of another
    This answer is identical to mine:
    Code:
    UPDATE tStockOut, tRemoved
    SET tStockOut.Phase = tRemoved.Phase
    ,  tStockOut.Line = tRemoved.Line
    , tStockOut.Machine = tRemoved.Machine

  6. #6
    Join Date
    Mar 2003
    Posts
    225
    i have no idea, but your example now seems to do the job...

    Thanks for your help.

    Andy

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think this is an example where abstracting the problem creates more issues than it solves. Using an imaginary schema, and so converting to and from the the actual schema, adds an unnecessary additional layer of complexity.
    Fundamentally, troubleshooting is actually about eliminating variables not introducing them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •