Update date from select

Question

I have a query i wrote that displays the information from two tables based on a join.

i need to update columns Phoneumber & email in table A
based on the join results from table B.

How do i do that ?

Select [NAME1]
      ,[WY_Email_and_Cells].[dbo].[WYEmailsCells].[PhoneNumber]
	  ,[WY_Email_and_Cells].[dbo].[WYEmailsCells].[Email]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[FirstWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[SecondWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ThirdWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ForthWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[FifthWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[SixWord]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[name2]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[MAILADDRESS1]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[MAILCITY]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[MAILSTATE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[MAILZIPCODE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ADDRESS]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[CITY]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ZIPCODE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[JURISDICTION]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[PARCELNO]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ACCOUNTNO]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[OCCCODE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[OCCPERCENT]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[PROPERTYTYPE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[OCCDESCRIPTION]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ABSTRACTDESCRIPTION]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ACTUALVALUE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ASSESSEDVALUE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[BATHCOUNT]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[BEDROOMCOUNT]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[BLTASDESCRIPTION]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[BLTASSTORIES]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[BLTASYEARBUILT]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[CLASSDESCRIPTION]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[CONDOIMPSF]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[HVACTYPE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[IMPCONDITIONTYPE]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[IMPEXTERIOR]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[IMPINTERIOR]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[IMPNO]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[IMPQUALITY]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[NETSF]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[ROOFCOVER]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[SF]
      ,[CheyenneProperties2023].[dbo].[SplitNames].[SaleDate]
       
  FROM [CheyenneProperties2023].[dbo].[SplitNames]

   left JOIN [WY_Email_and_Cells].[dbo].[WYEmailsCells]
  ON [CheyenneProperties2023].[dbo].[SplitNames].[FirstWord] = 
  [WY_Email_and_Cells].[dbo].[WYEmailsCells].[LastName] and
  [CheyenneProperties2023].[dbo].[SplitNames].[SecondWord] = 
  [WY_Email_and_Cells].[dbo].[WYEmailsCells].[FirstName] and
  [CheyenneProperties2023].[dbo].[SplitNames].[MAILZIPCODE] = 
  [WY_Email_and_Cells].[dbo].[WYEmailsCells].[Zip] and
  [CheyenneProperties2023].[dbo].[SplitNames].[MAILSTATE] = 
  [WY_Email_and_Cells].[dbo].[WYEmailsCells].[State]

  where FirstWord is not null and SecondWord is not null and ThirdWord is not null and thirdword <'LLC' and ForthWord is null and MAILSTATE='WY'
  

Answer 1

Hi @Elad R

Please refer to this sample:

DECLARE @TableA TABLE (ID_A INT,VAL1 INT)
DECLARE @TableB TABLE (ID_B INT,VAL2 INT)
INSERT INTO @TableA VALUES (1,NULL),(2,NULL),(3,NULL)
INSERT INTO @TableB VALUES (1,111),(2,222),(3,333)

;WITH CTE AS
(
 SELECT * 
 FROM @TableA A JOIN @TableB B ON A.ID_A=B.ID_B
)
UPDATE CTE SET VAL1 = VAL2

--OR
UPDATE A
SET A.VAL1 = B.VAL2
FROM @TableA A JOIN @TableB B ON A.ID_A=B.ID_B

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.