Insert and Update at the same time

[spikepl]

I have a table tblDestination that I want to update.

I have data in tblSource. If SourceID exists in tblDestination, the values in that record should be updated. If not, then that record should be added, with a new PK.

I can do this in severals steps, no problemo. But I seem to recall there is a possibility to do all this in one go with some fancy Left linking etc. Am I wrong?

tblDestination
--------------
DestID (PK - autonumber)
SomeData (Text)


and

tblSource
-----------
SourceID (PK - long)
SomeSourceData (Text)

so with

tblDestination
---------------
DestID SomeData
1............text1
2............text2

and

tblSource
----------
SourceID SomeSourceData
1...............text25
7...............text46

end result is

tblDestination
--------------
DestID SomeData
1............text25
2............text2
3............text46

 

[vbaInet]

I can do this in severals steps, no problemo. But I seem to recall there is a possibility to do all this in one go with some fancy Left linking etc. Am I wrong?

No fancy native Left linking in Access to accomplish your objective I'm afraid. In other platforms yes but maybe it can be accomplished with a pass through query to the database Server (for example).

 

[JANR]

No fancy native Left linking in Access to accomplish your objective I'm afraid.

Not exactly true, you can both Update old records and insert new records with the same UPDATE query using an outer join with no where-clause.

ex:

UPDATE tblSource LEFT JOIN tblDestiantion ON tblSource.SourceID = tblDestiantion.DestID SET tblDestiantion.DestID = [tblSource].[SourceID], tblDestiantion.SomeData = [tblSource].[SomeSourceData];

However you will not be able to update the field DestID if this field is defined as an autonumber, Access would not allow it. The field must be defined as long integer the same as SourceID.

If you run this query instead:

UPDATE tblSource LEFT JOIN tblDestiantion ON tblSource.SourceID = tblDestiantion.DestID SET tblDestiantion.SomeData = [tblSource].[SomeSourceData];

It will change SomeData to match SomeSourceData, but will assign the next availible autonumber to any new records that does not exist.

regards

Jan R

 

[spikepl]

@Janr

May an abundance of goats, chickens, oxen and virgins rain upon you and your family

 

[JANR]

No problem Spike, I'll pop out to see if the rain has started

Jan R

 

[spikepl]

RUn query 3 in attached. No matter what I do, cannot make it come out with 3 rows, one updated, one unaffected and one added.

UPDATE tblDestination LEFT JOIN tblSource ON tblDestination.MyDestID = tblSource.MySourceID SET tblDestination.SomeText = [tblSource].[SomeSourceText], tblDestination.MyDestID = [tblSource].[mYSourceID];

I had painstakingly prepared an example here, but the stupid AWF-forum logged me out and threw all that away .... grrrrrrrrrrrr.

 

[JANR]

Switch the join to tblSource.SourceID = tblDestiantion.DestID

UPDATE tblSource LEFT JOIN tblDestination ON tblSource.SourceID = tblDestination.DestID SET tblDestination.SomeText = [tblSource].[SomeSourceText], tblDestination.MyDestID = [tblSource].[mYSourceID];

and you get this table:

 DestID MyDestID SOmeText  
 1 1 textS1  
 2    textD2  
 3 7 textS7

The reason for the blank MyDestID in record 2 is that it dos not exist in tblSource and shoulden't be edited.

Now on future runs your original JOIN would be correct since there now is not a connection between DestID and SourceID, the connection/link will be MySourceID and MyDestID

Hope this helps

Jan R

 

[spikepl]

Indeed!

Thank you very much.

 

[JANR]

I'v added some more information in my last post, hope it makes sence

Jan R

 

[vbaInet]

Not exactly true, you can both Update old records and insert new records with the same UPDATE query using an outer join with no where-clause.

Good job Jan! I would have to test it out later.
Interestingly it's not obvious in Access.