Update Statement & Stored Procedured

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 Statement & Stored Procedured

Author

Topic

hanifikri
Starting Member

14 Posts

Posted - 2007-05-21 : 04:23:23

Hi,

1) I want to create an update statement which is can update 4 tables at one time (What i mean is in one update statement can update 4 tables). Do you have any idea how to do it or i have to create 4 update statement in order to update 4 tables (Refer below for my update statement).

/*******************************************/
/* UPDATE Job Movement Table */
/*******************************************/
update hrsc_jobmov
set grptodt = grp.retiredt
from hrsc_employee a
JOIN hrsc_grpperson grp ON
grp.personid = a.personid
JOIN hrsc_jobmov jmov ON
jmov.compnycode = a.compnycode
and jmov.staffno = a.staffno
and jmov.todt = '01/01/3000'
where a.status = 'A'
and jmov.emptypcodeto = 'T'
and ( a.staffno not like 'B%'
and a.staffno not like 'PR%' )
and grp.retiredt <= getdate()

/************************************************/
/* UPDATE Location Movement Table */
/************************************************/
update hrsc_locmov
set grptodt = grp.retiredt
from hrsc_employee a
JOIN hrsc_grpperson grp ON
grp.personid = a.personid
JOIN hrsc_jobmov jmov ON
jmov.compnycode = a.compnycode
and jmov.staffno = a.staffno
and jmov.todt = '01/01/3000'
JOIN hrsc_locmov lmov ON
lmov.compnycode = a.compnycode
and lmov.staffno = a.staffno
and lmov.todt = '01/01/3000'
where a.status = 'A'
and jmov.emptypcodeto = 'T'
and ( a.staffno not like 'B%'
and a.staffno not like 'PR%' )
and grp.retiredt <= getdate()

/**********************************************/
/* UPDATE Salary Movement Table */
/**********************************************/
update hrsc_salmov
set grptodt = grp.retiredt
from hrsc_employee a
JOIN hrsc_grpperson grp ON
grp.personid = a.personid
JOIN hrsc_jobmov jmov ON
jmov.compnycode = a.compnycode
and jmov.staffno = a.staffno
and jmov.todt = '01/01/3000'
JOIN hrsc_salmov smov ON
smov.compnycode = a.compnycode
and smov.staffno = a.staffno
and smov.todt = '01/01/3000'
where a.status = 'A'
and jmov.emptypcodeto = 'T'
and ( a.staffno not like 'B%'
and a.staffno not like 'PR%' )
and grp.retiredt <= getdate()

/*****************************************/
/* UPDATE Employee Table */
/*****************************************/
update hrsc_employee
set resigndt = grp.retiredt,
iresigndt = grp.retiredt,
lastwrkdt = dateadd(day,-1,grp.retiredt),
rsgnrsncode = 'BERSARA',
status = 'R'
from hrsc_employee a
JOIN hrsc_grpperson grp ON
grp.personid = a.personid
JOIN hrsc_jobmov jmov ON
jmov.compnycode = a.compnycode
and jmov.staffno = a.staffno
and jmov.todt = '01/01/3000'
where a.status = 'A'
and jmov.emptypcodeto = 'T'
and ( a.staffno not like 'B%'
and a.staffno not like 'PR%' )
and grp.retiredt <= getdate()

2) I want to schedule this update to be run every midnight. So the best way to do is to create a stored procedured and schedule it to be run every midnight. If i have to create 4 update statements for 4 tables.. do i have to create 4 stored procedure also? or i just create 1 stored procedure to run all the 4 updates?

Please advise.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-21 : 05:07:15

Each update statement can update only one table. Create a single stored proc that performs all four updates.

Mark

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 06:48:57

Why are you storing the same date in all four tables?

Can't you JOIN to an appropriate table to get the date, and thus only store it once? (or use VIEWs to expose the date if that makes Queries easier)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources