Update with sub select - 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)

Update with sub select

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-01-17 : 11:34:09

Hi i'd like to update a table column to the value of '1' with the values i get returned from my record set.

Example

Update a
set col1 = '1'
(select min(a),min(b),c
from tbl
group by a,b,
having c = '1')

Is this correct ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-17 : 12:12:30

No. What do you want to do ? Try posting some sample data & the result that you want

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-01-18 : 06:50:51

I think i got this to work what i wanted was to get the min sequence_no and date from logs in my system each log is in the system more than once but has a sequence number and datetime stamp on it. First of all i got all the min sequence and min dates this gave me my record set, (Which is all the first valid logs) i put this into a temp table then i Joined this temp table back to the main table where i extracted the data out of. I updating a indactor in this main to set it to '1' to give me all the min logs.

Here is a example. If anyone see any problem with it let me know thanks.

--drop table #tbl1
--drop table #tbl2

create table #tbl1
(
sequence_no int,
Event_date nvarchar(20),
log_no nvarchar(20),
ind int
)

create table #tbl2
(
sequence_no int,
Event_date nvarchar(20),
log_no nvarchar(20),
ind int
)

insert into #tbl1(sequence_no,Event_date,log_no,ind)
values('1','2004-11-29 08:47:34','114475766',null)
insert into #tbl1(sequence_no,Event_date,log_no,ind)
values('2','2004-11-29 09:12:34','114475766',null)
insert into #tbl1(sequence_no,Event_date,log_no,ind)
values('3','2004-11-29 09:17:34','114475766',null)

-- Insert the record set results into #tbl2
-- this is all the min logs in the system
insert into #tbl2(sequence_no,Event_date,log_no,ind)
select min(sequence_no), min(Event_date),log_no,ind
from #tbl1
group by log_no,ind

-- update the ind column in tbl1 were they join with the result in tbl2
update #tbl1
set #tbl1.ind = '1'
from #tbl1 inner join #tbl2 on (#tbl1.sequence_no = #tbl2.sequence_no
and #tbl1.Event_date = #tbl1.Event_date and #tbl1.log_no = #tbl1.log_no)

-- Result should return a ind = 1 where i have the min sequence number and min date..
select *
from #tbl1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-18 : 09:12:31

You can skip the #tbl2 with by joining to a derived table


update	t1
set	t1.ind	= '1'
from	#tbl1 t1 inner join
	(
		select	sequence_no = min(sequence_no), Event_date = min(Event_date), log_no, ind
		from #tbl1
		group by log_no,ind
	) t2
	on	t1.sequence_no 	= t2.sequence_no
	and	t1.Event_date	= t2.Event_date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources