Hi Jay,

My preference is to use JOIN to join tables and WHERE for filter conditions. IMO, it makes SQL statements easier to read.

UPDATE	TMP
		SET		CurrentDeliveryDate = TMP2.ActualDeliveryDate
				,LastDeliveryCost = 1
FROM #TMPACCT TMP	
	INNER JOIN #TMPACCT2 TMP2 ON TMP.OrderID = TMP2.OrderID 
		WHERE TMP2.DescType = 0

>
>A quick question for the SQL-experienced. Which way is preferable to link two tables in an UPDATE statement, a JOIN or a WHERE clause - or is there a notable difference? I've always leaned towards using JOINs, but a colleague is pushing the other way. Is there a best practice that applies here?
>
>

>UPDATE	#TMPACCT
>SET		CurrentDeliveryDate = TMP2.ActualDeliveryDate
>		,LastDeliveryCost = 1
>FROM	#TMPACCT2 TMP2
>	INNER JOIN #TMPACCT TMP ON TMP.OrderID = TMP2.OrderID and TMP2.DescType = 0
>

>
>- OR -
>
>

>UPDATE	#TMPACCT
>SET		CurrentDeliveryDate = TMP2.ActualDeliveryDate
>		,LastDeliveryCost = 1
>FROM	#TMPACCT2 TMP2
>	WHERE #TMPACCT.OrderID = TMP2.OrderID and TMP2.DescType = 0
>