Updating dates from one table to another

Transact-SQL
1

A user wants us to update a table called Final Subdel Database with a file they are going to start giving to us on a semi annual bases. I am not sure how to do this without messing anything up. Any advice would be great.

Let me give you an example, here is the query below from the table I need to update. The only two fields that I will need to update will be "Initial Credentialing" and "Last Credentialing Date". The primary key in both tables would be the "PCS Number".

Select Distinct
[PCSNumber],[Sub Del Name],[Last Name],[First Name],[M# Initial],Degree,[Social Security], DOB],Specialty,NPI,TIN,Type,[Initial Credentialing Date],[Last Credentialing date]
From Final_Subdel_Database

The date fields are nvarchar.

This is where it get tricky for me.

Criteria....

  1. If the date on the master database " Final_Subdel_Database" is current compared to the one that is on the new file "SubdelJanJune" then we are suppose to keep the date on the master database " Final_Subdel_Database".

  2. If the date on the new file called "SubdelJanJune" is current then I need to override the date that is on the Master table.

3> If the date on the New file called "SubdelJanJune" is NULL then keep what is on the master file or if the Master file is null add the date from the new file.
.
How can I do this without making duplicate lines and just updated that field that is linked to that PCS Number?

Thanks in advance!

2

If you are just using an update statement and the PCSNumber is unique (that is, one row per PCSNumber), there's no way updating the date will cause duplicate rows.

If PCSNumber is not unique, then what is unique? What is the Primary Key?

Basically you'd want something like this:

UPDATE f
SET f.[Initial Credentialing Date] = u.SubdelJanJune
FROM FinalSubdel AS f
JOIN UserFile u ON u.PCSNumber = f.PCSNumber
WHERE f.[Initial Credentialing Date] IS NOT NULL
    AND (
        f.[Initial Credentialing Date] > u.SubdelJanJune
        OR u.SubdelJanJune IS NULL
        )

   ON f.PCSNumber = u.PCSNumber