update with select top 1 - 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 2005 Forums

Transact-SQL (2005)

update with select top 1

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2010-08-17 : 17:42:31

I have a vacancy table tblVac:

id posID startdate enddate
1 1 1/1/2010 2/2/2010
2 2 3/3/2010
3 1 5/5/2010

where start is date when a position (posID) becomes vacant and enddate is when it becomes filled. Obviously in the life of a position, it can be vacant and filled many times. If the enddate is null, and the position table record's status is 'V' then it is not filled.

I need to update table jobs withthe most recent vacant date for a position whose status = 'V'

Jobs table

ID posID vacantDate posStatus
1 1 'V'
2 20 'A'

I have tried two varieties of update with select, but neither work because of all the where clauses. here is what I have tried:

UPDATE jobs
SET vacantdate=( SELECT top 1 tblVac.startdate
FROM tblVac v
WHERE v.positionID = jobs.positionID and jobs.posStatus = 'V' and v.enddate is null order by v.startdate desc )
from tblVac v

and the other attempt:

UPDATE jobs
SET vacantdate = ( SELECT top 1 tblVac.startdate
FROM tblVac
WHERE tblVac.posID = jobs.posID and jobs.posstatus = 'V' and tblVac.enddate is null order by startdate desc )
WHERE EXISTS
( SELECT top 1 tblpositionvacancy.startdate
FROM tblVac
WHERE tblVac.posID = jobs.posID and jobs.posstatus = 'V' and tblVac.enddate is null order by tblVac.startdate desc )

Is there a way to do this with one update sql
or
Maybe I have to break this up in some way, create a temp table from tblvac with all the POSID's that have enddate = null and their most recent startdate. Then link this with the jobs table.

Thanks for any help

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-08-18 : 00:12:38

Im a little confused, but a setup like this might help us understand. Let me know what the desired result is in this form:



 declare @tblVac table (id int primary key, posID int, startdate datetime, enddate datetime)
 insert into @tblVac
	select 1, 1, '1/1/2010', '2/2/2010' union all
	select 2, 2, '3/3/2010', null union all
	select 3, 1, '5/5/2010', null

declare @Jobs table (ID int, posID int, vacantDate datetime, posStatus char(1))
insert into @Jobs
	select 1, 1, null, 'V' union all
	select 2, 20, null, 'A'

select * from @Jobs	

;with cte_LastVacant (posId, VacantDate)
as	(	select posId, max(StartDate) from @tblVac group by posId)

update j 
set vacantDate = c.VacantDate
from	@Jobs j 
join	cte_LastVacant c on j.posId = c.posId
where	j.posStatus = 'V'

select * from @jobs

Sachin.Nand

2937 Posts

Posted - 2010-08-18 : 00:28:33

[code]
declare @tblVac table (id int primary key, posID int, startdate datetime, enddate datetime)
insert into @tblVac
select 1, 1, '1/1/2010', '2/2/2010' union all
select 2, 2, '3/3/2010', null union all
select 3, 1, '5/5/2010', null

declare @Jobs table (ID int, posID int, vacantDate datetime, posStatus char(1))
insert into @Jobs
select 1, 1, null, 'V' union all
select 2, 20, null, 'A'

select * from @Jobs

select * from @tblVac

update j set j.vacantDate=(
select top 1 startdate from @tblVac V where V.posID=j.posID
and j.posStatus='V' and v.enddate is null order by startdate desc
)from @Jobs j

select * from @Jobs
[/code]

Check the red part marked above with your query.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

smh
Yak Posting Veteran

94 Posts

Posted - 2010-08-18 : 13:52:44

Both solution are correct in what I had wanted. They are great examples on updating with select statements. I appreciate your time and effort.

Before seeing these, I ended up creating a second table and joining it, but now I will go back and do it in one sql.

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources