Update query with a subsquery - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update query with a subsquery

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2004-04-22 : 17:08:39

This is table Emp_Name
id Name nTickets
10 Dominic 0
20 Kimberly 0
30 Tony 0

This is table Emp
Ticket id
abc 10
def 10
gsf 20
ghi 30

I want to update the field nTickets in Emp_name by counting the number of tickets in table Emp. I tried this:

UPDATE EMP_NAME
SET nTickets = n.nTickets
FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket,
FROM EMP
GROUP BY id) as e
ON n.id = e.id

It didn't work. It complained about the prefix "n". Somehow I managed to change it to:

UPDATE EMP_NAME
SET nTickets = n.nTickets
FROM (SELECT * FROM EMP_Name) as n
JOIN ( SELECT id, Count (Ticket) as nTicket,
FROM EMP
GROUP BY id) as e
ON n.id = e.id

Now it "worked", but after 10 hours it was still "working".

Some questions:
1. Why did the second query work, but not the first?
2. What is the proper way to do this type of update, using a subquery?

Thanks,
Dom

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 17:20:13

Here ya go:



UPDATE en
SET nTickets = t1.NumOfTickets
FROM Emp_name en
INNER JOIN (
	SELECT id, COUNT(Ticket) AS NumOfTickets
	FROM Emp
	GROUP BY id ) t1
ON en.id = t1.id

Tara

DOlivastro
Starting Member

41 Posts

Posted - 2004-04-22 : 17:30:54

Thanks, Tara. Worked like a charm. But I'm still confused about something. The only difference between your sql and my first attempt is that you used "UPDATE <aliasName>" and I used "UPDATE <tablename>". Why did this make a difference at all? And why did my second query take such a long time.

Dom

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-22 : 17:31:45

Yours shouldn't even have compiled. I do not know why it just ran and ran.

Tara

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-23 : 08:43:37

Aliases: Keep up with them.

UPDATE EMP_NAME
SET nTickets = n.nTickets
FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket,
FROM EMP
GROUP BY id) as e
ON n.id = e.id

Think you needed this:

UPDATE EMP_NAME
SET n.nTickets = e.nTickets
FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket,
FROM EMP
GROUP BY id) as e
ON n.id = e.id

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-23 : 09:27:58

I think you mean....

UPDATE EMP_NAME
SET n.nTickets = e.nTicket <-- note the difference!

FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket,
FROM EMP
GROUP BY id) as e
ON n.id = e.id

DOlivastro
Starting Member

41 Posts

Posted - 2004-04-23 : 10:23:01

Actually, at least using Query Analyzer (Comes with SQL Server), you can not have a prefix on the left side of the equal sign in the SET clause.
That is, you can not say: SET e.nTickets = n.nTickets

You must say: SET nTickets = n.nTickets

The left side of the equal sign must always be a column in the table mentioned in the UPDATE clause. You can not change this with a prefix.

Tara had the right answer, it seems. You must have an alias name in the UPDATE clause, if an alias is used. I just don't know why.

Dom

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-23 : 12:12:14

Andrew and I were talking about the Right side not the left. You aliased EmpName as n and then "SET nTickets = n.nTickets" essentially back to itself BUT for the fact that you did not JOIN EmpName back to itself as well and I'm guessing there is a pseudo-cartesian effect going on. Additionally and I did not catch this at first neither of your queries as posted should have worked:

UPDATE EMP_NAME
SET nTickets = n.nTickets
FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket, <--comma not needed
FROM EMP
GROUP BY id) as e
ON n.id = e.id

UPDATE EMP_NAME
SET nTickets = n.nTickets
FROM (SELECT * FROM EMP_Name) as n
JOIN ( SELECT id, Count (Ticket) as nTicket, <--comma not needed
FROM EMP
GROUP BY id) as e
ON n.id = e.id

both syntax errors.

Tara's query is correct, but this compiles as well

UPDATE Emp_Name
SET nTickets = e.nTickets
FROM EMP_Name as n
JOIN ( SELECT id, Count (Ticket) as nTicket
FROM EMP
GROUP BY id) as e
ON n.id = e.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources