I have a below query in SQL server which i need to convert into a snowflake compatible query.

 UPDATE t1 set t1.RMId = null from Client t1 
inner join Main t2  on t1.Code=t2.Registration_No 
inner join Mapping t3  on  t2.ID=t3.Id
 where  isnull( t1.Code,'')!=''  and t1.date >=  Convert(date,Getdate() - 30)

I’m not sure how to use multiple joins in snowflake , or what happens when there is no joining condition between source and target table. Below is the query I’ve written so far, but I’m not sure how it will work or gives expected result as same as above query from sql server

UPDATE Client t1
SET t1.RMId = NULL
FROM ( SELECT * from Client t1
INNER JOIN Main t2 ON t1.Code = t2.Registration_No
INNER JOIN Mapping t3 ON t2.ID = t3.Id ) t2
WHERE COALESCE(t1.Code, '') != ''
  AND t1.date >= CURRENT_DATE() - 30;