Need SQL Statement

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

Guest

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
 
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
 
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.
 
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.
 
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?
 
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
 
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
 
Back
Top