Need help with a 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

General SQL Server Forums

New to SQL Server Programming

Need help with a update

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-01 : 13:00:16

I have 4 tables assignment, Customer, employee and project. In the project table I want to update the numofCustomer, in the project table, by finding all of the customerid that are apart of the projectID.

Assignment table (bridge table for both Customer and Project)

Customer table
Customerid
CustomerType
CustomerHours
ProjectID

Project table
ProjectID
ProjectName
numofCustomer

I thought that i might do

update Project
Set numCustomer = (The number of CustomerID for each project)

I found a way to show how many customer are on each project but do not know how to us that number I get to add to the right project.

Select Proj_num, Count(CustomerID)
From Assignment
Group by Proj_Num

Thanks for any help

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-01 : 13:26:29

If ProjectID is Part of Customer, that's not good

Can 1 Customer belong to many Projects?

And I assume you asignment table carries ProjectID and CustomerID

Also, why store the number of customers associated to a project on the projectId...it is stale data as soon as you update it

I suggest you create a view

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-01 : 13:26:50

Wait

Is this Homework?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-01 : 13:39:04

I have been trying to do this it has not been working right. I want it to add the number of customer already on the assignment table.

UPDATE Project
SET NumofCustomer = ( Select Proj_num, Count(CustomerID)
From Assignment
Group by Proj_Num)
WHERE EXISTS
( Select Proj_num, Count(CustomerID)
From Assignment a
Join Project p
on p.proj_num = a.proj_num
Group by Proj_Num);

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-01 : 13:40:26

No I am trying to work all the problem in the back of the chapter to get better at doing alter, update, modify and create triggers but this stuff keeps kicking me in the butt.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-01 : 14:07:32

UPDATE P
SET NumofCustomer = num.NumOfcustomer
FROM
Project P
INNER JOIN

( Select Proj_num, [NumOfCustomer] = Count(CustomerID)
From Assignment
Group by Proj_Num) num

ON

p.proj_num = num.proj_num

Jim

Everyday I learn something that somebody else already knew

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-01 : 19:15:42

is [NumOfCustomer] an array?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-02 : 07:20:34

It's the column name I gave to the "Count(CustomerID)", the column must have a name.
It should probably be written "Count(CustomerID AS NumOfCustomer", but I'm set in my ways

Jim

Everyday I learn something that somebody else already knew

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-02 : 11:18:29

Oh I thought that you can use arrays in SQL queries.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-02 : 12:43:05

There are no arrays in sql server

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-02 : 15:44:54

When I saw [NumOfCustomer], I thought that sql can use arrays. That would be cool.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-02 : 21:04:57

quote:
Originally posted by intern2424

When I saw [NumOfCustomer], I thought that sql can use arrays. That would be cool.

Yes, they're called tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-12-06 : 16:01:53

haha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources