14
 insert into tableA (column1) 
 select min(tableC.column1)
 from tableB 
 inner join tableC on (tableC.coumn2 = tableB.column1
 and tableB.column2 = tableA.column2) 
 group by tableA.column2

How would I change the above to a update with group by instead of insert with group by based on the criteria tableB.column2 = tableA.column2 ?

Note that I am using SQL SERVER 2008.

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
Chen Lu
  • 205
  • 1
  • 2
  • 9

1 Answers1

32
  Update A set Column1 = minC    
    from (select Ab.Column2, min(C.Column1) as minC
            from A Ab
            inner join B on Ab.Column2 = B.Column2
            inner join C on C.column2 = B.Column2 --No need to add again the A.col2 = B.col2
            group by Ab.Column2) Grouped where A.Column2 = Grouped.Column2

Is this what you want? This will get for each columnA the C.Column1 min value, and will update it in A.Column1 (that's where you were inserting before), based on condition A.Column2 = Grouped.Column2.

here is a SQL-Fiddle Demo

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
  • another question though: if table A is defined as @A, then how come it returned me an error for the last line of the query:Must declare the scalar variable "@A", and how would i fix this issue? – Chen Lu Sep 10 '12 at 13:25
  • That's another question, you should created with an example of the declarated table, and the query you made. Probably is a problem of alias, or the scope of the table – Gonzalo.- Sep 10 '12 at 13:41
  • The problem is right now i cannot write update @A sometable ... where sometable.column2 = Grouped.column2 and everything else stays the same. – Chen Lu Sep 10 '12 at 14:50
  • what is "@a sometable"? after update word, you should put a table name, or an alias, not both – Gonzalo.- Sep 10 '12 at 14:55
  • posted another question at http://stackoverflow.com/questions/12362141/are-there-any-differences-between-the-results-of-these-2-queries – Chen Lu Sep 11 '12 at 10:45