SELECT into an UPDATE
-
How do I turn this statement into an UPDATE that updates COL5 when SUM(COL4)>5? COL4 cannot be in the GROUP BY.
SELECT COL1, COL2, COL3, SUM(COL4) FROM TABLE1
GROUP BY COL1, COL2, COL3
HAVING SUM(COL4)>5
I know that I can do this by putting the results of this query into a temporary table, then updating TABLE1 off of the temp table, but I was wanting to do this in one statement.
I know that I can set this statement up as a subquery and aggregate COL1+COL2+COL3 and do my updates on that, but I was looking for a little simplier version.
Thanks in advance for any responses to this post.
Live to Throw
Throw to Live
Will Summers -
Does TABLE1 have a primary key? If so what is it?
What are we updating Col5 to?
update table1 set col5 = 'X' where pk in (
select pk from table1 t
join
(
SELECT COL1, COL2, COL3, SUM(COL4) as s
FROM TABLE1
GROUP BY COL1, COL2, COL3
HAVING SUM(COL4)>5
) v
on t.col1 = v.col1 and t.col2 = v.col2 and t.col3 = v.col3
)
-
Updating COL5 to a text value. I have simplified the query for ease of posting.
That's actually very very similar to what I was trying to explain that I already have. I didn't do the join, but a subquery. I will try the join and see if it's any faster.
I was hoping that there was a simplier way, but I guess not, unless someone else has a better idea.
Live to Throw
Throw to Live
Will Summers -
You could try something like this as well:
update table1 set
col5 = s.colsum
from
table1 t
inner join (
select
s.col1,
s.col2,
s.col3,
sum(s.col4) as colsum
from
table1 s
group by
s.col1,
s.col2,
s.col3
having
sum(s.col4) > 5
) s
on (t.col1 = s.col1
and t.col2 = s.col2
and t.col3 = s.col3)
-
I goth the following error doing it Lynn's way which is why I added the extra step with the primary key.
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 's' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 's' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 's' does not match with a table name or alias name used in the query.
-
I'd need to see your code, mrpolecat. I just tested my code in both SQL Server 2000 and SQL Server 2005, and didn't get any errors. I have used derived tables like this quite often with no problems.
-
I copied your code from here into query analyzer in SQL 2000 thinking I had a syntax issue when I tried it. Here it is copied back out from QA.
update table1 set
col5 = s.colsum
from
table1 t
inner join (
select
s.col1,
s.col2,
s.col3,
sum(s.col4) as colsum
from
table1 s
group by
s.col1,
s.col2,
s.col3
having
sum(s.col4) > 5
) s
on (t.col1 = s.col1
and t.col2 = s.col2
and t.col3 = s.col3)
You don't need to waste your time as this wasn't my question and I certainly believe it worked for you.
-
Not wasting my time, just curious. I cut and paste directly from your post to QA and it ran no problem. There could be differences in how our systems are configured that may be causing the difference. I don't plan on spending any more time on it than I already have.
Maybe we will find other differences as well as we assist others, just something for us to keep in the back of our heads.
-
I'd suggest not to use the same alias twice in a query, Lynn. You are using "s" for both Table1 and the derived table. Not only it is easy to make a mistake when writing it, it also makes the query harder to understand later when someone has to do maintenance.
Otherwise, everything is OK - I had no problem running your query in QA.
-
Vladan,
Normally, I don't use the same table alais inside the derived table and and the derived table. I guess I was working on autopilot when I was working on it. I will try to be more diligent on future suggestions.
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply