Simple? - SQL Server Forums

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

Simple?

Author

Topic

Yellowdog
Starting Member

34 Posts

Posted - 2009-04-15 : 19:01:37

I will start by saying I know this should be basic but I am stumped.

I have a table that one column needs to be updated based on two others.

This is what I have and I am sure you know what I am trying to do but I cannot wrap my brain around what I need to do to get it done, so here goes.

update dssalesdata2
	set gprofit = (select cast(price as money) - cast(vehcost as money) from dssalesdata2)
where gprofit = '' and price <> '' and vehcost <> ''

you can see I am trying to update the entire gprofit column with the differance of the two others, unfortunatly I cannot seem to figure out how. I know that the select in the set statement returns a result set, but I left it there so you could see what I am trying to do.

Any help (links or a push) would be great!

Cheers

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-04-15 : 19:29:29

1. Why don't u use a computed col

2. try to replace:

where isnull(gprofit,'') = ''

with:

where gprofit = ''

Shimmy

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-15 : 22:03:06

please post sample data and required results

-----------------------------------------------------------------------------------------------
Ashley Rhodes

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-16 : 08:56:16

update dssalesdata2
set gprofit = select cast(price as money) - cast(vehcost as money)
where gprofit = '' and price <> '' and vehcost <> ''

But consider just selecting this (computed column or view) and use proper types. If your columns are money then set them to be money and use NULL where appropriate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 10:41:09

you want to update entire rows of table with same value? thats what you're doing above. i think what you might need is this


update dssalesdata2
	set gprofit = cast(price as money) - cast(vehcost as money)
where gprofit = '' and price > '' and vehcost > ''

Yellowdog
Starting Member

34 Posts

Posted - 2009-04-16 : 10:56:34

Thanks for the replies,

This is exactly what I needed

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 11:24:25

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources