SQL Update with Join

I do not think you can update a non-existent table.
si2 is the result of this query:
(Select Macola_Number, Spec_Version_Num, Effective_Date from Specifications_Import2 si
            where Effective_Date IS NULL) si2

What do you get from
select si.Macola_Number, si.Spec_Version_Num, si.Effective_date,
            si2.Macola_Number, si2.Spec_Version_Num, si2.Effective_date
            from Specifications_Import2 si
Left join
     Specifications_Import2 si2
            on si.Macola_Number = si2.Macola_Number and si.Spec_Version_Num = si2.Spec_Version_Num (and si.Effective_date is Null )
where si.Effective_Date is not null

with and without the items in parenthesis

If I understand it, you have duplicates (but not completely as some have the effective_date as null) in your table and you seem to try to actually complete the duplicate that should be there before you go through a process to remove duplicates (you want to be identical...)?

Will this version work for you?

UPDATE s1
SET s1.Effective_Date = (SELECT Effective_Date 
			FROM Specifications_Import2 s2
			WHERE s1.Macola_Number = s2.Macola_Number and s1.Spec_Version_Num = s2.Spec_Version_Num and s2.Effective_Date is not null)
FROM Specifications_Import2 s1
WHERE s1.Effective_Date IS NULL

Select allOpen in new window

Worked out the solution with help from the guys.