SQL Server Update with group by

 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.

Answers


  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


Need Your Help

How to add static final field with initializer using ASM?

java assembly bytecode java-bytecode-asm bytecode-manipulation

I want to add static final field into .class file using ASM, and the source file is

Spring 3.0.5 doesn't evaluate @Value annotation from properties

spring frameworks properties autowired

Trying to auto-wire properties to a bean in Spring 3.0.5.RELEASE, I'm using: