Update data in one table from another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update data in one table from another

Author

Topic

sharapov
Starting Member

1 Post

Posted - 2006-06-19 : 21:48:21

I have 2 tables in my Access database. Only some of the columns in both of the tables are matching. I need to update table #1 with some records from table #2 using the following criteria.

If (for example) UserFName and UserLName doesn't exist in table one and Occupation (field) is blank, then copy that record from table #2 into table #1. Can anybody help me out with that query?

Thanks.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-19 : 23:38:57

Easiest way to do it:


INSERT INTO table1 (UserFName, UserLName, Occupation, more fields...)
SELECT t2.UserFName, t2.UserLName, t2.Occupation, more fields....
FROM   table2 t2 LEFT JOIN table1 t1 ON t1.UserFName = t2.UserFName AND t1.UserLName = t2.UserLName
WHERE  t1.UserFName IS NULL AND t2.Occupation IS NULL

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-21 : 03:51:36

By the way, when you say "..Occupation (field) is blank,.." that could mean that its space (not null). if that is the case, change's timmy last condition from


t2.Occupation IS NULL


NZ(t2.Occupation,"") = ""

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources