updating 1 table from another table

[Rob]

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

[Bob Barrows]

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"