Professional Web Applications Themes

updating 1 table from another table - ASP Database

Hi all, I'm trying to update one table in an access db from another table in the same db. I'm sure it must be possible to do it in one go in asp. table1 date user shift ot table2 user shift ot I'm wanting to update table1 which already has values in its date field and update the user, shift and ot with values from table2. Table2 is basically a teamplate of data with 42 rows and when updating into table1 i'd need to specify the date range of the rows to update. I've tried several combinations like the following; ...

  1. #1

    Default updating 1 table from another table

    Hi all,

    I'm trying to update one table in an access db from another table in the
    same db. I'm sure it must be possible to do it in one go in asp.

    table1
    date
    user
    shift
    ot


    table2
    user
    shift
    ot

    I'm wanting to update table1 which already has values in its date field and
    update the user, shift and ot with values from table2.
    Table2 is basically a teamplate of data with 42 rows and when updating into
    table1 i'd need to specify the date range of the rows to update.


    I've tried several combinations like the following;

    strSQL = "UPDATE table1 table1.user, table1.shift, table1.ot WHERE DATE
    BETWEEN #dfrom# AND #dto# SELECT * from template;"
    or
    strSQL = "UPDATE table1 set table1.user = table2.user,
    table1.shift=table2.shift, table1.ot=table2.ot WHERE DATE BETWEEN #dfrom#
    AND #dto# SELECT * from template;"

    Any ideas?
    Many thanks in advance,
    Rob


    Rob Guest

  2. #2

    Default Re: updating 1 table from another table

    Rob wrote:
    > Hi all,
    >
    > I'm trying to update one table in an access db from another table in
    > the same db. I'm sure it must be possible to do it in one go in asp.
    >
    > table1
    > date
    > user
    > shift
    > ot
    >
    >
    > table2
    > user
    > shift
    > ot
    >
    > I'm wanting to update table1 which already has values in its date
    > field and update the user, shift and ot with values from table2.
    > Table2 is basically a teamplate of data with 42 rows and when
    > updating into table1 i'd need to specify the date range of the rows
    > to update.
    >
    >
    The best way to figure this out is to open your database in Access and use
    the Query Builder to create your query. Here's a step-by-step:
    1. In the Queries tab, click the "Create query in Design View"
    2. Select your two tables and close the dialog
    3. Click and drag the date field from one table field list to the other to
    create the join
    4. Go to Query|Update Query in the menu to change it to an update query
    Optional:
    I like to use table aliases so:
    1. Right-click table1 and select Properties if the Properties
    dialog is not already visible
    2. Change the alias from "table1" to d (for destination)
    3. Click into the Source box just to maqke sure your
    alias sticks.
    4. Highlight table2 and set its alias to s (for source)
    5. Click and drag the date field from the table1 (or d) list into the Field
    row of the first column of the grid.
    6. In the Update To row, type "s.[user]" (without the quotes of course), or
    "table2.[user]" if you decided not to use table aliases
    7. Repeat for the rest of the columns that need updating
    8. Switch to SQL View to see what it looks like. Something like this:

    UPDATE Table1 AS d INNER JOIN table2 AS s ON d.date = s.date
    SET d.[user] = [s].[user], d.shift = [s].[shift];

    or this if you didn't use table aliases:

    UPDATE Table1 INNER JOIN table2 ON Table1.date = Table2.date
    SET Table1.[user] = [Table2].[user], Table1.shift = [Table2].[shift];

    My advice is to ALWAYS use the query builder to create and test your queries
    before attempting to run them from asp (or any other client application)

    Incidently, this is one of the differences between JetSQL and Transact-SQL
    (SQL Server). The corresponding Transact-SQL query would look like this:

    UPDATE d
    SET d.[user] = [s].[user], d.shift = [s].[shift]
    FROM Table1 AS d INNER JOIN table2 AS s ON d.date = s.date


    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows Guest

Similar Threads

  1. Replies: 0
    Last Post: September 16th, 04:37 PM
  2. Replies: 0
    Last Post: September 15th, 05:39 AM
  3. Replies: 0
    Last Post: September 10th, 10:49 PM
  4. Replies: 0
    Last Post: September 10th, 05:33 PM
  5. Replies: 0
    Last Post: September 10th, 05:29 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139