Jack Jack - 2 years ago 71
SQL Question

Update, Insert and Delete rows in a table based on changes in another table

I have Table A

ID | Address | Field1 | Field2 | etc
1 | 1 Smith St | a | sd | sdf
2 | 2 Smith St | b | sf | ff

I have created another table Table B with just the ID and Address and 2 extra fields for Latitude and Longitude which I populate with a separate script

ID | Address | Lat | Long
1 | 1 Smith St | 20.23 | 110.34
2 | 2 Smith St | 18.34 | 112.43

There are 2 things I would like to do.

  1. Check for new records in table A based on the ID field and insert ID, Address into table B as a new row (important)

  2. Check for changes to the Address field for records in Table A which are already in Table B and update them and clear the lat/long values in table B if the address has changed (not as important but would be nice)

Table A gets updated with a Drop/Create so I don't need to listen for changes I'm just looking for an update/insert query which could do one or both of these. Can be 2 separate queries as well.

I've manged to solve the first part based on the following post, sql insert rows from another table based on condition sorry I thought I had searched enough for an answer. Still unsure on how to go about the 2nd part. Using SQL Server 2014 to address the comments. The following takes care of the first part for me. How can I update the rows in TableB where TableB.ID = TableA.ID but the Address fields are different?

FROM TableA t

Answer Source

NOTE - all code below is for MS-SQL, since you didn't specify.

To check for new records in Table A that are not in Table B, based on ID, and insert them into TableB

Insert Into TableB (ID, Address)
Select A.ID, A.Address
From TableA A
    Left Join TableB B on B.ID = A.ID
Where B.ID is null;

Part 2 - to update TableB address and clear lat, long if Id matches but address is different

Update B set Address = A.Address, Lat = NULL, Long = NULL
From TableB B
    Inner Join TableA A on A.ID = B.ID
Where A.Address <> B.Address;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download