-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You're updating the table tbl_hours_new w/ it's own data. You say you
want to update/replace data in the "second table." From the context of
that paragraph I'm assuming that you want to update tbl_hours_Palm1 (the
second table you mentioned) w/ data from tbl_hours_new (the first table
you mentioned). If so then try this (these queries will only update
current data. It will not add new data):
UPDATE tbl_hours_Palm1
SET [Date] = (Select [Date] from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id),
Employee = (Select Employee from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id),
Job = (Select Job from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id),
Hours = (Select Hours from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id),
PalmID = (Select PalmID from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id)
WHERE exists (Select * from tbl_hours_new
where tbl_hours_new.id = tbl_hours_Palm1.id);
If you want to update tbl_hours_new w/ data from tbl_hours_Palm1:
UPDATE tbl_hours_new
SET [Date] = (Select [Date] from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id),
Employee = (Select Employee from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id),
Job = (Select Job from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id),
Hours = (Select Hours from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id),
PalmID = (Select PalmID from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id)
WHERE exists (Select * from tbl_hours_Palm1
where tbl_hours_new.id = tbl_hours_Palm1.id);
Note: In JET SQL the column name delimiter is a square bracket and not
a single quote. In standard SQL the column name delimiter is a double
quote.
If you want to add new data try this:
INSERT INTO <target table> ([Date], Employee, Job, Hours, PalmID)
SELECT [Date], Employee, Job, Hours, PalmID
FROM <source table> As S LEFT JOIN <target table> As T
ON S.id = T.id
WHERE T.id IS NULL
Replace <target table> w/ the name of the table you want to insert data
into, and replace <source table> w/ the name of the table you want to
get the data from.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQnZ9noechKqOuFEgEQKfbACgyldj6aaFWhFJOA8ueCBmnm qQmp8AoPYU
dwE+V6RVxdRe2uUjBKWz5xD0
=lZm4
-----END PGP SIGNATURE-----
JellyMan wrote:
I am trying to do something with an Access SQL query that I have never
done. I can see how it SHOULD work in my head, but can't seem to make
it work in reality. I think I have it close, but my SQL syntax must be
off.
What I WANT to do:
I have two tables. One (tbl_hours_new) is an SQL linked table through
ODBC, which reads just like any other table in Access. The second
(tbl_hours_Palm1) is just a regular table. I want to be able to run a
single query that will read the data in the first table and update (or
replace) the information is the second table based on the id number
(which will be identical).
Here is my current SQL statement:
UPDATE tbl_hours_new SET
`Date` = (Select `Date` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
Employee = (Select `Employee` from tbl_hours_new where
tbl_hours_new.id = tbl_hours_Palm1.id),
Job = (Select `Job` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
Hours = (Select `Hours` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id),
PalmID = (Select `PalmID` from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id)
WHERE exists (Select id from tbl_hours_new where tbl_hours_new.id =
tbl_hours_Palm1.id);
Can anyone tell me where I have gone wrong?
