March 1, 2012 at 7:33 pm
I have a column that is currently empty (NULL). I have to update all rows (thousands) based on a calculation related to some other column in the same table. I have to update all the rows all at once. How can i achieve this?
Please suggest. The normal updates process don't seem to work.
create table t1 (price money, Finalprice money)
insert into t1 values (345,NULL),(435,NULL),(678,NULL)
i am trying to populate FinalPrice rows based on the calculations from price without using computed column.
March 1, 2012 at 7:35 pm
Isnt it straight forward UPDATE?
Update t1
set finalprice = price * 100
March 1, 2012 at 7:53 pm
However try to use isnull(Pricemoney,1) * 100 if you have got any null related columns on the pricemoney. This way the result won't be null.
March 1, 2012 at 7:58 pm
baabhu (3/1/2012)
However try to use isnull(Pricemoney,1) * 100 if you have got any null related columns on the pricemoney. This way the result won't be null.
That depends on the business requirement. Assume this, a shop added a new product but they still are negotiating on the price for that, and if this update uses ISNULL and updates the finalprice to some number, then the product will be assinged a new price. We dont want that.
So get the requirments clear form the owners and then use ISNULL.
March 1, 2012 at 8:10 pm
ColdCoffee (3/1/2012)
Isnt it straight forward UPDATE?
Update t1set finalprice = price * 100
My bad i din't do a good job in replicating the situation. that was simple update.
my problem is this.
update dt
set wrange= (select convert(varchar,datepart(m,min(CrsApp2.DtAd)))+'/'+ convert(varchar,datepart(d,min(CrsApp2.DtAd)))
+ ' - ' +
convert (varchar,datepart(m,min(DATEADD(DD, 6, CrsApp2.DtAd)))) +'/'+convert (varchar,datepart(d,min(DATEADD(DD, 6, CrsApp2.DtAd))))
FROM dt
CROSS APPLY ( SELECT DATEDIFF(WK,0,dates) ) CrsApp (DtDf)
CROSS APPLY ( SELECT DATEADD(WK ,CrsApp.DtDf ,-1 ) ) CrsApp2 (DtAd)
group by dates,CrsApp2.DtAd
order by dates)
Error Reads: Msg 1033, Level 15, State 1, Line 12
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
using TOP command doesn't help either.
March 1, 2012 at 9:07 pm
I have seen that query somewhere 😀
ANyways, i can what you are trying to do, but why do u need GROUP BY in your code? for every date, you need to update wrange correct? then just use whatever you have in SELECT in you curren statemtn in UPDATE's SET clause!
March 1, 2012 at 9:22 pm
Are you trying do something like this?
IF OBJECT_ID('TempDB..#Sample') IS NOT NULL
DROP TABLE #Sample ;
CREATE TABLE #Sample
( Dates DATETIME , wrange VARCHAR(20)) ;
INSERT INTO #Sample (Dates)
SELECT '01-MAR-2012'
UNION ALL SELECT '30-JAN-2012'
UNION ALL SELECT '19-DEC-2011'
UNION ALL SELECT '22-JUN-2007'
UNION ALL SELECT '05-AUG-2006'
UNION ALL SELECT '21-MAR-2009' ;
SELECT Dates , wrange
FROM #Sample ;
BEGIN TRAN
UPDATE Base
SET Base.wrange = CrsApp4.WkStrt + ' - ' + CrsApp4.WkEnd
FROM #Sample Base
CROSS APPLY (
SELECT DATEDIFF(WK,0,Base.Dates)
) CrsApp (DtDf)
CROSS APPLY (
SELECT DATEADD(WK ,CrsApp.DtDf ,-1 )
) CrsApp2 (WkStrt)
CROSS APPLY (
SELECT DATEADD(DD, 6, CrsApp2.WkStrt)
) CrsApp3 (WkEnd)
CROSS APPLY (
SELECT CONVERT(VARCHAR(5), CrsApp2.WkStrt , 101) ,
CONVERT(VARCHAR(5), CrsApp3.WkEnd , 101)
) CrsApp4( WkStrt, WkEnd)
SELECT Dates , wrange
FROM #Sample ;
ROLLBACK TRAN
IF OBJECT_ID('TempDB..#Sample') IS NOT NULL
DROP TABLE #Sample ;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply