How do I write an update with a case and join.

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

General SQL Server Forums

New to SQL Server Programming

How do I write an update with a case and join.

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-08 : 04:45:39

This is the select I want to use to update:

select
case
when "FB32 Area platta på mark/källargolv" <> 0 then
2 * 3.5 / (pi() * sqrt(6/25.00 * "FB32 Area platta på mark/källargolv") + 0.3 + 3.5 * "FB33 Summa R värmemotstånd" + 0.5 * convert(float, "FB42 Area källarvägg under mark") / (convert(float, "FB42 Area källarvägg under mark") + "FB46 Källarväggens totala area") * "FA19Kallare")
* log(pi() * sqrt(6/25.00 * "FB32 Area platta på mark/källargolv") / (0.3 + 3.5 * "FB33 Summa R värmemotstånd" + 0.5 * convert(float, "FB42 Area källarvägg under mark") / (convert(float, "FB42 Area källarvägg under mark") + "FB46 Källarväggens totala area") * "FA19Kallare") + 1)
else
NULL
end
from ss_fb, ss_fa
where ss_fb."Byggnadens ueNr" = ss_fa.FXByggnadensUEnr

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 04:48:11

Which column of which table do you want to update?

Madhivanan

Failing to plan is Planning to fail

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-08 : 04:59:48

Its an empty column in the samt table that is going to show a calculated sum based on the select.

Lets call the column: FBSum
Table to update is SS_FB

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 05:07:59

Try

update fb
set FBSum=
case
	when "FB32 Area platta på mark/källargolv" <> 0 then
	2 * 3.5 / (pi() * sqrt(6/25.00 * "FB32 Area platta på mark/källargolv") + 0.3 + 3.5 * "FB33 Summa R värmemotstånd" + 0.5 * convert(float, "FB42 Area källarvägg under mark") / (convert(float, "FB42 Area källarvägg under mark") + "FB46 Källarväggens totala area") * "FA19Kallare")
	* log(pi() * sqrt(6/25.00 * "FB32 Area platta på mark/källargolv") / (0.3 + 3.5 * "FB33 Summa R värmemotstånd" + 0.5 * convert(float, "FB42 Area källarvägg under mark") / (convert(float, "FB42 Area källarvägg under mark") + "FB46 Källarväggens totala area") * "FA19Kallare") + 1)
else
	NULL
end
from ss_fb as fb inner join ss_fa as fa
on ss_fb."Byggnadens ueNr" = ss_fa.FXByggnadensUEnr

You have strange column names

Madhivanan

Failing to plan is Planning to fail

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-08 : 05:12:40

Thanks for fast reply.

Get the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ss_fb.Byggnadens ueNr" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ss_fa.FXByggnadensUEnr" could not be bound.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-08 : 05:15:52

The last line should be

on fb."Byggnadens ueNr" = fa.FXByggnadensUEnr

Madhivanan

Failing to plan is Planning to fail

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-08 : 05:29:33

Thanks, Now I have to deal with "division by zero"