select and update - 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)

select and update

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2003-05-29 : 16:31:10

can you do a select and update just like you would a select and insert meaning is this possible

select org_id,total from completed
update reports set comp_tot = total where org_id = rep_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-29 : 16:37:06

No you can not do that. But what exactly are you trying to do though?

Is this what you are trying to do?:

UPDATE reports
FROM reports r
INNER JOIN completed c ON r.rep_id = c.org_id
SET r.comp_tot = c.total
WHERE c.org_Id = r.rep_id

Tara

csphard
Posting Yak Master

113 Posts

Posted - 2003-05-29 : 16:41:18

What i'm trying to do.

I have to produce a report.
I wanted to generate a listing of what was completed, whats overdue, and whats due.

I thought i would first search for all section numbers and insert that and the date.

Then i would search for whats completed and then update the correct sections.

Then i would search for whats due and then update the correct section

then i would search for whats over due and then update the correct section.

Maybe my thought process is wrong are there any suggestion about how i should do this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-29 : 16:46:56

Where are you inserting the section numbers? Into another table? You should be able to do this in one select and not even have to insert the data somewhere else and then update the data. Without the table structure, sample data, and other relevant info though it's kinda hard to give a suggestion.

Tara

csphard
Posting Yak Master

113 Posts

Posted - 2003-05-29 : 17:08:28

all this information is being put into the following table

CREATE TABLE [mnth_report] (
[m_id] [int] IDENTITY (1, 1) NOT NULL ,
[m_sec] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_ann_due] [int] NULL ,
[m_prob_due] [int] NULL ,
[m_ann_overdue] [int] NULL ,
[m_prob_overdue] [int] NULL ,
[m_ann_completed] [int] NULL ,
[m_prob_completed] [int] NULL ,
[m_rep_date] [datetime] NULL CONSTRAINT [DF_mnth_report_m_rep_date] DEFAULT (getdate())
) ON [PRIMARY]
GO

-gives me all of my
insert into mnth_report(m_org_id)
select org_id from new_organization order by org_id

-- the following select statement works
select e.sect as esect,d.eval_type as etype,count(*) as ctotal
from due_evals d,emp_information_test e
where d.datetobefiled between '01/01/2003' and '01/31/2003'
and e.empid = d.empid
and d.payloc_comp_date is not null
and e.bureau = '375' group by e.sect,d.eval_type

-- the following does not work. i wanted to put those answer back into
--mnth_report using the following statement

update mnth_report set m_ann_completed = ctotal
where esect = m_org_id
and etype = 'Annual'

csphard
Posting Yak Master

113 Posts

Posted - 2003-05-29 : 17:42:45

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-29 : 18:36:51

DECLARE @CTotal INT

select @CTotal = count(*)
from due_evals d,emp_information_test e
where d.datetobefiled between '01/01/2003' and '01/31/2003'
and e.empid = d.empid
and d.payloc_comp_date is not null
and e.bureau = '375' group by e.sect,d.eval_type

update mnth_report
set m_ann_completed = @CTotal
from mnth_report m, emp_information_test e
where e.sect = m.m_org_id
and e.type = 'Annual'

You might be able to do this without a variable though. Hopefully someone else will respond about this.

If you look in SQL Server Books Online and go to UPDATE..UPDATE (described), you will see a very good example in Example C.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources