Double Update

Yamini · January 13th, 2009, 08:29 AM

Hi! i've 2 Sql tables:Projects ProjectPhases Projects has Cols projID,StartDate,ProjName,a,b,c,d where a,b,c,d can take null values ProjectPhases has cols projphaseID,ProjID,ProjPhaseName,EndDate now i'm trying to write an update statement updating the cols a,b,c,d say for instance for projID =5 i also need the update the End date in Projectphases where projid is (obviously) a FK..

Code:

 create procedure proc{@ProjIDint, @a varchar(34), @b varchar(34), @c varchar(34), @d varchar(34) @EndDate datetime}  IF  EXISTS (SELECT 1 FROM Projects  WHERE ProjID = @ProjID)    update Projects   set a=@a,                              b=@b,                              c=@c,                              d=@d,                              p.enddate=@enddate from projects inner join projectphases as p on Projects.ProjID = p.projid  where Projects.ProjID = @ProjID

....for the obvious reson that its wrong it DOESN'T WORK...dnt they have an emoticon 4 crying??!@#....

Old Pedant · January 13th, 2009, 04:22 PM

PLEASE use LINE BREAKS in your code postings!

Code:

create procedure proc{ 
    @ProjIDint, 
    @a varchar(34), 
    @b varchar(34), 
    @c varchar(34), 
    @d varchar(34),  -- you had MISSING COMMA ON THIS LINE!!!
    @EndDate datetime}  
AS -- you were MISSING the AS keyword
IF EXISTS (SELECT 1 FROM Projects  WHERE ProjID = @ProjID)    
    update Projects set a=@a, b=@b, c=@c, d=@d, p.enddate=@enddate 
    from projects inner join projectphases as p 
    on Projects.ProjID = p.projid  
    where Projects.ProjID = @ProjID

And no, you can *NOT* do that. You must use two separate UPDATEs.
One per table.

Yamini · January 15th, 2009, 02:39 AM

it gives me a multipartidentifier p.enddate cud not b bound error...hey! let me get this correct .My tables go like this Projects: ProjID|ProjName|ProjTypeId|StartDate|EndDate|a|b|c |d ProjectPhases(PP): ProjPhaseID,ProjPhaseName|ProjID|EstimatedEndDate. .n so on.. the deal is i'm working in a 6 sigma project so it have these 5 phases DMAIC..so in the same presentation layer i need to update cols a,b,c,d n also the 5 phases' EstimatesEndDate for a Particular projid(i perfectly realize the same proj id wud be updated 5 times for each phase in the PP table)..its like phase D has a txtbox n a calenderctrl n Phase M has another one n so on...all the 5 phases' time lines need to be updated ...i know i can write separate updates in the same proc bt since both the tables have ProjID as a common field i'm wondering if it cud be done in a single update .....thanks for any kind of enlightenment that will prove my boss wrong!

Old Pedant · January 15th, 2009, 06:15 PM

At least as of SQL Server 2000, you can *NOT* update fields in more
than one table using a single UPDATE statement.

I *doubt* that has changed in 2005 or 2008, but why not check the
docs to be sure?

But, really, I think you already have your answer, from the error message:
multipartidentifier p.enddate cud not b bound
Isn't that saying what I did???

Old Pedant · January 15th, 2009, 06:20 PM

It's a minor point, but there's really no reason to use IF EXISTS.

If the id doesn't exist, the UPDATE will do nothing. So why bother??

Code:

create procedure proc{ 
    @ProjID int, 
    @a varchar(34), 
    @b varchar(34), 
    @c varchar(34), 
    @d varchar(34),
    @EndDate datetime}  
AS
UPDATE Projects set a=@a, b=@b, c=@c, d=@d
WHERE ProjID = @projid  

UPDATE projectphases SET enddate=@enddate 
WHERE ProjID = @projid

Why work harder than that???