2 Replies - 530 Views - Last Post: 03 August 2016 - 09:30 PM

#1 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

multi-part identifier error

Posted 03 August 2016 - 10:53 AM

Hello guys;

I ran in to an odd issue where I have to update an extra column with 1 as active 0 as inactive if the dates exceed the dates assigned to the date variables.

IF OBJECT_ID(N'dbo.ARCIVE_PROGRAMS') IS NOT NULL
DROP PROCEDURE dbo.ARCIVE_PROGRAMS
GO

CREATE PROCEDURE dbo.ARCIVE_PROGRAMS
AS
DECLARE
@ACTIVE INT = NULL,
@P_SDATE DATE = '01-01-2014',
@P_EDATE DATE = '12-24-2014'
SET NOCOUNT ON

SET @ACTIVE = 1
UPDATE PROGRAM.PROGRAM_INFO SET ACTIVITY = @ACTIVE
UPDATE PROGRAM.PROGRAM_DETAILS SET ACTIVITY = @ACTIVE
UPDATE CAMPUS.TEACHER SET ACTIVITY = @ACTIVE

SET @ACTIVE = 0
UPDATE PROGRAM.PROGRAM_INFO 
SET P.ACTIVITY = @ACTIVE, 
PD.ACTIVITY = @ACTIVE, 
T.ACTIVITY = @ACTIVE
FROM PROGRAM.PROGRAM_INFO AS P
INNER JOIN PROGRAM.PROGRAM_DETAILS AS PD ON PD.PROGRAM_DETAIL_ID = P.PROGRAM_DETAIL_ID
INNER JOIN CAMPUS.TEACHER AS T ON T.PROGRAM_INFO_ID = P.PROGRAM_INFO_ID
WHERE P.P_SDATE > @P_SDATE AND P.P_EDATE > @P_EDATE

SELECT * FROM PROGRAM.PROGRAM_INFO
SELECT * FROM PROGRAM.PROGRAM_DETAILS
SELECT * FROM CAMPUS.TEACHER




the error that I have been getting is as follows:

Msg 4104, Level 16, State 1, Procedure ARCIVE_PROGRAMS, Line 41
The multi-part identifier "P.ACTIVITY" could not be bound.


I need to make sure activity column gets updated as per expected before I can write this in transaction. I would really appreciate help on this.

thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: multi-part identifier error

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2342
  • View blog
  • Posts: 9,393
  • Joined: 03-December 12

Re: multi-part identifier error

Posted 03 August 2016 - 11:04 AM

Check the syntax over. You are updating, not selecting data.

My link
Was This Post Helpful? 0
  • +
  • -

#3 Nitewalkr  Icon User is offline

  • D.I.C Lover

Reputation: 149
  • View blog
  • Posts: 1,045
  • Joined: 17-November 10

Re: multi-part identifier error

Posted 03 August 2016 - 09:30 PM

Thank you astonechipher;

I know that was a noob attempt I was trying to update all of the same columns in multiple tables using one table for condition, if all of those multiple tables are related. Doing this in a stored proc.
Appreciated the link that you have provided but it didn't work. I will continue on trying it out.

Anyways, here is what I ended up doing.
it is pretty basic

--ADD A SEPARATE COLUMN TO ENABLE AND DISABLE THE ROW UPON CERTAIN CONDITION.
USE STUDENT_INFORMATION
GO

IF OBJECT_ID(N'dbo.ARCIVE_PROGRAMS') IS NOT NULL
DROP PROCEDURE dbo.ARCIVE_PROGRAMS
GO

CREATE PROCEDURE dbo.ARCIVE_PROGRAMS
AS
DECLARE
@ACTIVE INT = NULL,
@P_SDATE DATE = NULL,
@P_EDATE DATE = NULL

SET NOCOUNT ON

SET @ACTIVE = 1
UPDATE PROGRAM.PROGRAM_INFO SET ACTIVITY = @ACTIVE
UPDATE PROGRAM.PROGRAM_DETAILS SET ACTIVITY = @ACTIVE
UPDATE CAMPUS.TEACHER SET ACTIVITY = @ACTIVE

SET @ACTIVE = 0
SET @P_SDATE = '01-01-2014' 
SET @P_EDATE = '12-24-2014'

UPDATE PROGRAM.PROGRAM_INFO SET ACTIVITY = @ACTIVE
WHERE PROGRAM.PROGRAM_INFO.P_SDATE > @P_SDATE 
AND PROGRAM.PROGRAM_INFO.P_EDATE > @P_EDATE

UPDATE PROGRAM.PROGRAM_DETAILS SET ACTIVITY = @ACTIVE
WHERE EXISTS(SELECT * FROM PROGRAM.PROGRAM_INFO 
WHERE PROGRAM.PROGRAM_INFO.P_SDATE > @P_SDATE 
AND PROGRAM.PROGRAM_INFO.P_EDATE > @P_EDATE 
AND PROGRAM.PROGRAM_DETAILS.PROGRAM_DETAIL_ID = PROGRAM.PROGRAM_INFO.PROGRAM_DETAIL_ID)

UPDATE CAMPUS.TEACHER SET ACTIVITY = @ACTIVE
WHERE EXISTS(SELECT * FROM PROGRAM.PROGRAM_INFO 
WHERE PROGRAM.PROGRAM_INFO.P_SDATE > @P_SDATE 
AND PROGRAM.PROGRAM_INFO.P_EDATE > @P_EDATE  
AND CAMPUS.TEACHER.PROGRAM_INFO_ID = PROGRAM.PROGRAM_INFO.PROGRAM_DETAIL_ID)



This post has been edited by Nitewalkr: 03 August 2016 - 09:35 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1