Need Help writing Update Query

[smurfer]

Can anyone help write an update query based on two tables please:
table1 and table2 have exact same fields,
start date, end date, startPoint, and endPoint plus a few other fields...these 4 I gave are where the selection criteria comes into play..

I want to create an update query that takes data from table 2 and checks if all 4 values exist in table1, and if so updates the entire row in table1 with all values from table 2.

i.e.
startDT endDT startPT endPT rate
table1 6/1/2001, 6/10/2001, abcdef, defdef, 100
table2 6/1/2001, 6/10/2001, abcdef, defdef, 200

so it would check that those 4 values equal those in table1 and change table1's rate column value to 200...

I would then need to create an append query to add those that do not exist, but I am sure I can get that after looking at the update query...

thanks,
sm

 

[tlbroadbent]

You can actually do the Update and Insert in one query if you use an Outer Join rather than an Inner Join.

[tt]UPDATE table1 RIGHT JOIN table2
ON table1.startDt = table2. startDt
AND table1.EndDt = table2.EndDt
AND table1.startPt = table2.startPt
AND table1.EndPt = table2.EndPt
SET
table1.startDt = table2.startDt,
table1.endDt = table2.endDt,
table1.startPt = table2.startPt,
table1.endPt = table2.endPt,
table1.rate = table2.rate;[/tt]
Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[smurfer]

Terry,
Thanks very much for your assistance with this query...
Sm