Help with SQL please

[JoshuaEir]

I am trying to write sql and a query for something like this:

Update
table
set
table.col1 = other table.col1
table.col2 = other table.col2
from
table
inner join
other table
on
table.id = other table.id


however I don't want the relationship, what I really want is something like this psuedo code:


UPDATE tblbalance, tblbalance_work SET tblbalance.StartDate =
tblbalance_move.StartDate, tblbalance.enddate = tblbalance_move.enddate WHERE
tblbalance_move.StartDate = forms!frmbalanceutil!txtbegindate




I want the StartDate to set from the tblbalance_move StartDate


Thank you,
Josheir


The tblbalance.StartDate is the primary key, the other table (tblbalance_move) doesn't have a primary key.
I am trying to copy over the whole table, but this is the idea simplified. Both tables have the same fields.

[ssanfu]

I am trying to copy over the whole table, but this is the idea simplified. Both tables have the same fields.

You don't say why you want to copy the table, but you consider using an append query (Insert INTO...), not an update query.

[JoshuaEir]

The table only has one startdate for each record (its the primary key) I was having problems with the duplicate keys using the append query. How I understand it append doesn't write over the record, which is what I think I need.

JoshEir

[ssanfu]

I am trying to copy over the whole table, but this is the idea simplified. Both tables have the same fields.

I don't understand.
If you are going to copy over the whole table, why not delete all records from "tblbalance" and append the records from "tblbalance_move"?

Are the "startdates" in "tblbalance_move" unique?

If tblbalance.startdate and tblbalance_move.StartDate are a 1-to-1 relationship, start with

UPDATE tblbalance INNER JOIN tblbalance_move ON tblbalance.StartDate = tblbalance_move.StartDate SET tblbalance.enddate = [tblbalance_move].[enddate];

and add the rest of the fields.


What should happen if there are records (startdates) in "tblbalance_move.StartDate" that are not in "tblbalance.StartDate"?

The only other way I know of is to write VBA code to do the update.

[JoshuaEir]

A very smart idea!

Josheir