UPDATE ACCORDING TO DATE - 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

UPDATE ACCORDING TO DATE

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-27 : 23:24:05

I HAVE TWO TABLE LIKE

TBL_INF

PID RATE
1 15
2 16
3 20

TBL_DETAIL

PID RATE DATE
1 15 30-MAY-2012
1 18 1-JUN-2012
1 19 5-JUN-2012
1 22 18-JUN-2012
2 16 30-MAY-2012
2 18 1-JUN-2012
2 19 20-JUN-2012
3 20 05-JUN-2012
3 24 06-JUN-2012

NOW I NEED TO UPDATE TBL_INF COL RATE ON THE BASIS OF TBL_DETAIL DATE AND RATE.

IF CURRENT DATE IS = 05-JUN-2012 THEN

UPDATE TBL_INF
SET RATE = 15
WHERE PID = 1

PLS HELP

challenge everything

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 23:30:30

According to your data, where TBL_Detail.PID=1 and TBL_Detail.Date='05-JUN-2012', the TBL_Detail.Rate=19...why do you want to set the TBL_INF.Rate=15 based on that condition? Especially since the TBL_INF.RATE is already equal to 15.

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-06-27 : 23:35:00

SORRY ,MISTAKE IT SHOULD BE 19

challenge everything

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-06-27 : 23:57:33

Ok, so I'm just guessing this is what you want from what you posted. Any time you're doing an update, you will really need to check to make sure this is absolutely what you want to do...so I'd test it before making the change:

declare @TBL_INF table (pid int, rate int)
declare @TBL_DETAIL table (pid int, rate int, Sdate date)

insert into @TBL_INF (pid, rate)
values
(1, 15),
(2, 16),
(3, 20)

insert into @TBL_DETAIL (pid, rate, Sdate)
values
(1, 15, '30-MAY-2012'),
(1, 18, '1-JUN-2012'),
(1, 19, '5-JUN-2012'),
(1, 22, '18-JUN-2012'),
(2, 16, '30-MAY-2012'),
(2, 18, '1-JUN-2012'),
(2, 19, '20-JUN-2012'),
(3, 20, '05-JUN-2012'),
(3, 24, '06-JUN-2012')

--Check data prior to the update

select TI.pid, TI.rate, TD.rate, TD.SDate
from @TBL_INF TI
join @TBL_DETAIL TD on TD.pid=TI.pid

--Set your Check Date

declare @CheckDate date
set @CheckDate='05-JUN-2012'

--Update TBL_INF based on the date check

update @TBL_INF
set rate=D.rate
from @TBL_INF T
join @TBL_DETAIL D on D.pid=T.pid
where d.Sdate=@CheckDate

--Check data after doing the update

select TI.pid, TI.rate, TD.rate, TD.SDate
from @TBL_INF TI
join @TBL_DETAIL TD on TD.pid=TI.pid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources