upadate table in database from a temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

upadate table in database from a temp table

Author

Topic

Junior Sqler
Starting Member

18 Posts

Posted - 2013-07-09 : 11:23:30

Hello!

I have a #temp table with 3 columns : A1, B1 and C1
and a table named Basis in databaset with 3 columns:A2, B2 and C2

I want to update tha table on database based on values of temp as following:

update Basis set

C2 = (select a.C1 from #temp where #temp.A1 =Basis.A2),

B2 =(case when (Basis.C2 <=3 and (select a.B1 from #temp3 a where #temp.A1 =Basis.A2) <= 0.05 )then 1 else 0 end)
else 1 end)

It shows: Incorrect syntax near the keyword 'else'..
Any ideas about the mistake?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 11:39:00

In your second statement you updating B2 based on the existing value of Basis.C2 or the value as it exists after you have performed the first update (i.e., setting C2 to the value from #temp?) The following assumes the latter:

UPDATE b SET
	c2 = t.c1,
	b2 = CASE WHEN T.c1 <= 3 AND b1 < 0.05 THEN 1 ELSE 0 END 
FROM
	#temp T
	INNER JOIN Basis b ON T.A1 = b.A2;

Junior Sqler
Starting Member

18 Posts

Posted - 2013-07-10 : 03:43:07

thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources