Need SQL Statement

B

Bill Sturdevant

I have a permanent table and a temporary table:

Perm Table Temp Table
Fld A Fld B Fld A Fld B
aaaaa bbbbb ddddd eeeee
ddddd mmmmm nnnnn
ggggg hhhhh
mmmmm

What SQL command do I use to update Perm Table Fld B with only the new
values in Temp Table Fld B?

The result should look like:

Perm Table
Fld A Fld B
aaaaa bbbbb
ddddd eeeee
ggggg hhhhh
mmmmm nnnnn
 
F

Frank Stone

hi,
write an append query in design mode that would append the
temp table to the perm table. then on the tool bar
goto view>SQL. All the design mode querys do is write the
SQL statements with GUI
Reqards
Frank
 
B

Bill Sturdevant

Thanks, Frank. I tried that and could not come up with the correct query.
Since I know it is not desirable to post example databases, I asked the the
guidance in terms of a SQL statement.

I still need the help.
 
F

Frank Stone

i don't see why it wont work. i have used this techique a
number of times for use with the docmd.runSQL statement.
and for creating record sets.
 
B

Bill Sturdevant

Frank,

It is probably my ignorance that is making it not work. Can you give me an
example of a statement that does work and I will adapt it to my situation?
 
T

Ted Allen

Hi Guys,

Hope you don't mind if I jump in here. In reading Bill's Original post, I
think he may be looking for an update query rather than an append query.

Bill, you could try something like:

UPDATE [Perm Table] INNER JOIN [Temp Table] ON [Perm Table].[Fld A] = [Temp
Table].[Fld A] SET [Perm Table].[Fld B] = [Temp Table].[Fld B];

This will update all [Fld B]'s in the permanent table to match the value in
the temp table based on a join on Field A. If you want to restrict the
updates, to say only those records where Field B is blank in the permanent
table, you can add a WHERE condition such as:

UPDATE [Perm Table] INNER JOIN [Temp Table] ON [Perm Table].[Fld A] = [Temp
Table].[Fld A] SET [Perm Table].[Fld B] = [Temp Table].[Fld B] WHERE [Perm
Table].[Fld B] Is Null;

Hope that helps.

-Ted Allen
 
J

John Spencer (MVP)

Do all the values in TEMP.FldA have a corresponding Value in Perm.FldA? If so,
you can use an update statement.

UPDATE [Perm Table] INNER JOIN [Temp Table]
ON [Perm Table].[Fld A] = [Temp Table].[FLD A]
SET [Perm Table].[Fld B] = [Temp Table].[Fld B]
WHERE [Temp Table].[Fld B] Is Not Null <<-- If you need a where clause
 
Top