Update table with autoID

[kris9]

Hi,

I have a table with following fields

usrTable - ID(PK)(autonumber) , UsrID, Name, Date

tempTable - UsrID, Date

How can I create a update query to update data from tempTable to usrTable ? Fields to update are UsrID and Date.



Not sure how to update with ID(auto number).

Using Access 2010

[June7]

Why are you using tempTable?

Is UsrID unique in both tables?

Do you want to modify existing records in usrTable or do you want to add new records?

[kris9]

I am using the tempTable to bulk modify the Dates in usrTable.

UsrID is unique in both tables. Only need to modify the Date in UsrTable is UsrID matches.

Why are you using tempTable?

Is UsrID unique in both tables?

Do you want to modify existing records in usrTable or do you want to add new records?

[June7]

Still not clear why a tempTable. How is data entered into tempTable? Why not just edit usrTable directly?

The only common identifier field in both tables is usrID. These fields must be used to link the two tables. Cannot update usrID field. Can update Date field.

Try:

UPDATE tempTable INNER JOIN usrTable ON tempTable.UsrID = usrTable.UsrID SET usrTable.[Date]=tempTable.[Date];


BTW, should not use reserved words as names. Date is a reserved word.

[kris9]

thanks that works. Using the tempTable to all records to update then run the update query to update..


How do I update only if value in the tempTable is not blank ? I have few more fields in the same table. sometime i only have to update one field.. so if other fields are blank, it overwrites the data to blank.

Still not clear why a tempTable. How is data entered into tempTable? Why not just edit usrTable directly?

The only common identifier field in both tables is usrID. These fields must be used to link the two tables. Cannot update usrID field. Can update Date field.

Try:

UPDATE tempTable INNER JOIN usrTable ON tempTable.UsrID = usrTable.UsrID SET usrTable.[Date]=tempTable.[Date];


BTW, should not use reserved words as names. Date is a reserved word.

[June7]

Try a WHERE clause:

WHERE Not tempTable.[Date] Is Null

[kris9]

I have three fields in tempTable - field1, field2, field3. these match to fields in usrTable. I need to update one of these fields from time to time.

If I only have to update field2 and other fields are empty, it should not overwrite the related values in usrTable to empty.

Tried the Not Is Null but on all the 3 fields criteria but not updating any field.

[June7]

Post the sql statement you attempted. Or provide database for analysis. Follow instructions at bottom of my post.

[kris9]

UPDATE tblTemp INNER JOIN tblUsers ON tblTemp.UsrID = tblUsers.UsrID SET tblUsers.BU = tblTemp.[BU], tblUsers.Location = tblTemp.[Location], tblUsers.Segment = tblTemp.[Segment], tblUsers.Dept = tblTemp.[Segment]
WHERE ((Not (tblUsers.BU) Is Null) AND (Not (tblUsers.Location) Is Null) AND (Not (tblUsers.Segment) Is Null) AND (Not (tblUsers.Dept) Is Null));


If I want to only update Location from tempTable and all the other fields are blank, its not updating location from above..

[June7]

Correct. It will only update records that meet all of the given criteria. Try:

UPDATE tblTemp INNER JOIN tblUsers ON tblTemp.UsrID = tblUsers.UsrID SET tblUsers.BU = Nz(tblTemp.[BU], tblUsers.BU), tblUsers.Location = Nz(tblTemp.[Location], tblUsers.Location), tblUsers.Segment = Nz(tblTemp.[Segment], tblUsers.Segment), tblUsers.Dept = Nz(tblTemp.[Segment], tblUsers.Dept);