Merge tables and overwrite if necessary

[tommeekers]

I have two very similar tables in my database.
Table A contains user data which gets imported out of an Oracle database regularly. Table B contains the user data I use in my application.

Is it possible to insert table A into B, adding the records from A that do not yet exist in B (user ID does not exist) and updating only the name and address fields for the records that do already exist in B ? Or am I just asking too much now ?

 

[PHV]

Something like this ?
UPDATE TableB B RIGHT JOIN TableA A ON B.UserID = A.UserID
SET B.UserID = A.UserID, B.Name = A.Name, B.Address = A.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tommeekers]

Hmm, that query does update the records that already exist in table B, but it doesn't seem to import the records from A into B that do not yet exist in B.
I can't really figure out what these underlined parts are for, so I left them out. Maybe that's the problem?

UPDATE [u]TableB[/u] B RIGHT JOIN [u]TableA[/u] A ON B.UserID = A.UserID 
SET [u]B.UserID = A.UserID[/u], B.Name = A.Name, B.Address = A.Address

 

[tommeekers]

Do both my tables have to contain the same fields for this to work ? Cause right now table A contains way more fields then B, and when updating I just select the fields I need (name and address). Seems to work when the record exists, seems to give me an error with the "validation rules" when I try to add a record.