Update query criteria problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables tempTbl amd MasterTbl with the same fields. I need to
update the MasterTbl based on the TempTbl based on the following conditions:
a. if record in TempTbl is new i.e. username field and logdate are not in
the MasterTbl; append entire record
b. if tempTbl.username and TempTbl.logdate exists update changed fields.

Each MasterTbl record has only one unique username and logdate combination
per day.

--
Eric Brown
Technical Support
(e-mail address removed)
Wise Business Services Inc
214.915.9945 (Voice)
214.915.9947 (Fax)
 
You will have to do that in two passes -using two queries Since you do not
provide table details, this is a conceptual example

First, unless you have a way of telling which Columns (fields if you prefer)
have been changed - for example with a timestamp of some kind, you may be
better off just updating MasterTbl will all values in TempTbl where key
values match.

'first update existong records
Update M
Set M.Fld1 = t.Fld1, m.fld2 etc
from MasterTbl M Inner Join TempTbl T
on M.KeyFld1 = T.KeyFld1...

now insert new records
insert into MasterTbl
Select T.* from TempTbl T left Join MasterTbl M
on M.KeyFld1 = T.KeyFld1...
where m.Keyfld1 is null

HS
 
Thanks for the advice. I am having problems making my queries work. Here is
my table structure:

MasterTbl: TempTbl:
MasterID Primary
ProjID ProjID
USERNAME Username Primary Key
LoginDate LoginDate Primary Key
LoggedTime LoggedTime
SCANSTARTIMG SCANSTARTIMG
SCANCOUNT SCANCOUNT
INDEXCOUNT INDEXCOUNT
INDEXKEYSTROKES INDEXKEYSTROKES


The logic I need is this:
If UserName AND LoginDate exist update other fields
If UserName AND LoginDate do not match any other record then append.
 
Eric,

Here is the update query: - just paste this in the SQL view of a Query
Design and save it.
UPDATE M
FROM MasterTbl M
INNER JOIN TempTbl T
ON T.LoginDate = M.LoginDate
AND T.LoggedTime = T.LoggedTime
SET M.ProjID = T.ProjID,
M.USERNAME = T.USERNAME,
M.SCANSTARTIMG = T.SCANSTARTIMG,
M.SCANCOUNT = T.SCANCOUNT,
M.INDEXCOUNT = T.INDEXCOUNT,
M.INDEXKEYSTROKES = T.INDEXKEYSTROKES;

Re Appending rows: How do you plan to provide missing MasterIDs?
Assuming it is an autonumber column, you could create an append qiery as
follows:

INSERT INTO MasterTbl(ProjID, USERNAME, LoginDate, LoggedTime,
SCANSTARTIMG, SCANCOUNT, INDEXCOUNT, INDEXKEYSTROKES )
Select T.ProjID, T.USERNAME, T.LoginDate, T.LoggedTime, T.SCANSTARTIMG,
T.SCANCOUNT, T.INDEXCOUNT, T.INDEXKEYSTROKES
FROM TempTbl T
LEFT JOIN MasterTbl M
ON T.LoginDate = M.LoginDate
AND T.LoggedTime = T.LoggedTime
WHERE m.MasterID IS NULL

HS
 
Back
Top