update the table according to other table value

Question

update the table according to other table value

Frances L

SSCarpal Tunnel

Points: 4727
More actions
August 23, 2011 at 7:44 am

#150459

easy way to write this sp
I want to update one table field value according to other table value.
table a
ID Certfication
2 ACNS-BC
3 CCRN
if table a have CCRN, then I will update table B row with the id value as 2 column as CCRN 1, otherwise as 0
hard to translate to the sql
UPDATE [dbo].[tblNursingCertificationReport]
SET
[CCRN] = 1 ,ACNS-BC=1
WHERE
( select * from [NurProfileCertification] Where [emplID} =@emplid and Certification='CCRN')Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Frances L (8/23/2011)
easy way to write this sp
I want to update one table field value according to other table value.
table a
ID Certfication
2 ACNS-BC
3 CCRN
if table a have CCRN, then I will update table B row with the id value as 2 column as CCRN 1, otherwise as 0
UPDATE [dbo].[tblNursingCertificationReport]
SET
[CCRN] = 1
WHERE
( select * from [NurProfileCertification] Where [emplID} =@emplid and Certification='CCRN')
Thanks.

it kind of looks like hter eis a missing join criteria....

is the column [ID] in [Table a] mean the [EmployeeID]?(so Employee #2 has 'ACNS-BC'

and Employee #3 has 'CCRN'?

If that is true, i'd update something like this, i think:

UPDATE tblNursingCertificationReport

SET [CCRN] = 1

FROM NurProfileCertification

WHERE tblNursingCertificationReport.EMPLOYEEID = NurProfileCertification.EMPLOYEEID

AND Certification='CCRN'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SumCertification]

as

delete from tblNursingCertificationReport

declare @emplid char( 11 )

insert into tblNursingCertificationReport ([Name]

,[EmpID],LName ) SELECT distinct HREMP_adp.[FULL NAME] , NurProfileCertification.EmplID, HREMP_adp.LName

FROM NurProfileCertification INNER JOIN

HREMP_adp ON NurProfileCertification.EmplID = HREMP_adp.EMPLNO order by HREMP_adp.LName

set rowcount 0

select [EmpID] into #mytemp from tblNursingCertificationReport

set rowcount 1

select @emplid = EmpID from #mytemp

while @@rowcount <> 0

begin

set rowcount 0

select * from #mytemp where EmpID = @emplid

delete #mytemp where EmpID = @emplid

set rowcount 1

select @emplid = EmpID from #mytemp