need help with query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

need help with query

Author

Topic

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-05-24 : 16:25:54

I have this query:

Update table1
set [column1] =
Case
when month([created date]) =1 and b.[unit]='/day'
dayqty * b.rate
when month([created date]) =1 and b.[Unit charge]='/month' then
[monthqty] * b.[Rate]
end
From table1 a, Rate1 b where a.[id]=b.[id]

Is there a way to rewrite this query so it doesn't update [column1] with month([created date]) <> 1 to null value?

What i am trying to do is only update records in my condition and leave other records alone instead of updating record in my condition and updating other records that doesn't match the condition to null.

Any help would be greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-24 : 16:59:17

Will this work for you?


Update a set 
	[column1] = 
		Case 
		when b.[unit]='/day' then dayqty * b.rate
		when b.[Unit charge]='/month' then [monthqty] * b.[Rate]
		else [column1]
		end
From	table1 a
JOIN	Rate1 b	
		on a.[id]=b.[id] 
where	month([created date]) =1
AND	(b.[unit]='/day' OR b.[Unit charge]='/month')

Be One with the Optimizer
TG

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-05-24 : 17:07:59

I try this :

Update a set
[column1] =
Case
when b.[unit charge]='/day' then dayqty * b.rate
when b.[Unit charge]='/month' then [monthqty] * b.[Rate]
else [column1]
end
From table1 a
JOIN Rate1 b
on a.[id]=b.[id]
where month([created date]) =1

it worked - I don't really understand the purpose of the last statement:
" AND (b.[unit]='/day' OR b.[Unit charge]='/month') "
- please excuse my junior level.

Thanks

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-24 : 17:52:27

(b.[unit]='/day' OR b.[Unit charge]='/month')

Basically
IF the Columns unit = '/day' OR [Unit charge] = '/month' then include
b. is an alias of the Rate1 table, so wherever you see b.[id] it is actually referencing Rate1.[id]

Look up aliases in BOL

On another note your column names would better server you without spaces
eg. [created date] could be called Created_Date or CreatedDate, you then wouldnt need to use [] in your ALL your queries, except around reserved words (which isnt really good practice either)

Andy

Beauty is in the eyes of the beerholder

tocroi72
Yak Posting Veteran

89 Posts

Posted - 2005-05-27 : 09:53:25

Thank Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources