delete from #tmp

• astoughton

  SSC Journeyman

  Points: 79

  More actions

  January 9, 2002 at 1:52 pm

  #78232

  In reviewing stored procedures written by a predecessor Ive run into the following statement:

  -- Delete product instance from group

  -- **********************************

  DELETE xxx

  FROM #tmpDel

  WHERE yyy = #tmpDel.yyy

  I don't understand the structure of this statement because xxx appears to be a column name which isn't required/supported by a SQL DELETE statement.

  Soemone explained this is a special subselect needed when working with Temp tables but they couldn't explain any further.

  Can anyone point me to a reference? is this legitimate syntax.

  Thanks

  Al

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  January 9, 2002 at 2:07 pm

  #422030

  you can do this:

  delete x

  from product x

  where x.name = 'test'

  I don't think this is required and I've not used it to delete temp tables.

  The first delete works below, the second does not:

  create table #tmpDel (MyID int)

  insert #tmpDel select 1

  insert #tmpDel select 2

  select * from #tmpDel

  delete x

  from #tmpDel x

  where x.MyID = 1

  delete x

  from #tmpDel

  where x.MyID = 1

  drop table #tmpdel

  Steve Jones

  steve@dkranch.net