1

I have a table variable named @MatchTemp.I have written an insert query using select statement as shown below

Insert into @MatchTemp(StoreName,CompanyCode,StoreCode,Address,CityState)

Select s.StoreName,co.CompanyCode,s.StoreCode,s.BuildingName as Address,s.City+','+st.StateName as CityState 
from Cashsafes c
inner join Stores s on s.StoreId=c.StoreId
inner join Company co on co.CompanyId=c.CompanyId
inner join State st on st.StateId=s.StateId
and c.CashsafeId=@cashsafeid.

How can i write the update query instead of Insert as shown above? Im stuck here

Update @MatchTemp set StoreName=s.StoreName,CompanyCode=co.CompanyCode,.. 
from Cashsafes c

Any help will be appreciated.

DrCopyPaste
  • 4,023
  • 1
  • 22
  • 57
SparAby
  • 547
  • 1
  • 9
  • 25
  • You can't have a table named `@MatchTemp` at all. Temporary tables have names starting with `#` or `##`. Only table-valued parameters or variables can be defined with `@`. In any case, the `Update` statement should be the same as any other update statement, as long as the variable/parameter is in scope – Panagiotis Kanavos Apr 07 '14 at 13:06
  • @PanagiotisKanavos actually you can have such tables, but you are correct they are `not called temporary tables but table variables` – DrCopyPaste Apr 07 '14 at 13:07
  • @DrCopyPaste these aren't tables at all, they are variables. There is a huge difference – Panagiotis Kanavos Apr 07 '14 at 13:13
  • 1
    [Use an alias for the table variable. Example syntax here (works the same for normal tables)](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server). – Aaron Bertrand Apr 07 '14 at 14:02
  • 1
    @PanagiotisKanavos It is a table variable. It acts like a table in a lot of ways, including the ability to update. So I'm not sure what you're on about. – Aaron Bertrand Apr 07 '14 at 14:03
  • @AaronBertrand it wasn't clear from the original question (that only mentioned temp tables) what the problem was: using a non-existent temporary table, an out-of-scope variable or incorrect syntax of the UPDATE statement. – Panagiotis Kanavos Apr 07 '14 at 15:42

1 Answers1

2

the better is to use the temporary table(using # for scope or ## for global) rather than temporary variable as temporary variable gonna create in primary memory (RAM) whereas temporary table gonna create in secondary memory (DISK). However the syntax for your update query, you can append the where condion in last if required.

UPDATE @Table1 SET t1.name = t2.name
FROM Table2 t2
INNER JOIN @Table1 t1 ON t1.id = t2.id
iVad
  • 563
  • 2
  • 4
  • 13