• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4458
  • Last Modified:

UPDATE SELECT WITH GROUP BY

I have this sql statement which is not working, can u please explain why?
update table a set col1_timestamp=
(select max(b.col_timestamp) from  a,b
where a.col1=b.col2
and
a.col1='A'
and b.col2='C'
group by a.col3)
where exists
(select * from  a,b
where a.col1=b.col2
and
a.col1='A'
and b.col2='C'
group by a.col3)
I am getting sql0811, more than 1 value
0
ghp7000
Asked:
ghp7000
1 Solution
 
granbajoCommented:

 Mmm...  When you do max and then a group by, you will get 1 value for each result  of the group by clause.  In this case the max a.col1 for each different value of a.col3.  Having said that, you are asking for a.col1 and b.col2 to be of the same value, and the you ask for each one of them to have a different value, so I would expect that your query would not return anything.   Maybe there is something I am missing.

  What do you get as a result when you run the SELECT on its own?
0
 
ghp7000Author Commented:
thanks for responding to my post which is a completely stupid one as I only have about 10 thousand templates to work with that do what I want, my post just reflects my age, old and outdated (:
0
 
danirk2Commented:
Something similar to the following?

UPDATE t2
      set t2.price =
            (
                  SELECT SUM(cost) FROM t1      
                  WHERE t2.part = t1.part
                  GROUP BY part
            )
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now