41418

SQL Consolidating groups of data

Question:

I have the following data in a table

col1 col2 col3 276328 | 999999999999 | 664116 927356 | 999999999999 | 664140 927356 | 999999999999 | 664140 927356 | 999999999999 | 664159 927379 | 999999999999 | 664172

code:

create table #table (col1 bigint, col2 bigint, col3 bigint) insert into #table values(276328, 999999999999, 664116) insert into #table values(927356, 999999999999, 664140) insert into #table values(927356, 999999999999, 664140) insert into #table values(927356, 999999999999, 664159) insert into #table values(927379, 999999999999, 664172)

I need to update Col2 to the value in Col3 while grouping col1 and selecting the Min (col1) to apply to that sub group.

276328 and 927379 are on their own group but 927356 is a group that needs to have the lowest col3 number assigned to col2

So, the table above having been updated should look like this:

col1 | col2 | col3 276328 | 664116 | 664116 927356 | 664140 | 664140 927356 | 664140 | 664140 927356 | 664140 | 664159 927379 | 664172 | 664172

I have 2m+ rows to update so it has to be a bulk update not a loop.

How would I write the SQL to update the table?

Answer1:

declare @myTable table (col1 bigint, col2 bigint, col3 bigint) insert @myTable select 276328 , 999999999999 , 664116 union select 927356 , 999999999999 , 664140 union select 927356 , 999999999999 , 664140 union select 927356 , 999999999999 , 664159 union select 927379 , 999999999999 , 664172 update a set a.col2 = b.col3 from @myTable a inner join ( select col1, MIN(col3) col3 from @myTable group by col1 ) b on a.col1 = b.col1 select * from @myTable

Answer2:

update t set col2 = g.mincol3 from tbl t join ( select col1, min(col3) mincol3 from tbl group by col1 ) g on t.col1 = g.col1

Answer3:

If you are using MySQL

UPDATE table1 a INNER JOIN ( SELECT col1, minCol FROM ( SELECT col1, MIN(col3) minCol FROM tableName GROUP BY col1 ) c ) b ON a.col1 = b.col1 SET a.col2 = b.minCol

Answer4:

IF your RDBMS SQLServer2005+

UPDATE x SET x.col2 = x.col3 FROM (SELECT col2, MIN(col3) OVER (PARTITION BY col1) AS col3 FROm #table) x

Recommend

  • SQL Consolidating groups of data
  • SQL SELECT ORDER BY multiple columns depending on value of other column
  • Dereferenceing on casting the void pointer to float*/int*
  • How does socketcan handle arbitration?
  • SQL Count. How can I count how many distinct values are in a table when an other two columns are mat
  • Ordering a Union Query in MS Access SQL
  • Does Julia have something equivalent to Ans (Matlab) or Last.value (R)
  • cannot be assigned to — it is read only - C#
  • Listen to the 'change' even of an element in the shadow DOM
  • Notepad++ - delete all lines with certain text
  • TSQL Rolling Average of Time Groupings
  • change color of jstree node
  • distinct values from multiple fields within one table ORACLE SQL
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • How can Delete be both a DDL and a DML statement
  • How to 'create temp table as select' in Slick?
  • How to assign byte[] as a pointer in C#
  • pyodbc doesn't report sql server error
  • D3 get axis values on zoom event
  • Reduction and collapse clauses in OMP have some confusing points
  • ViewController With Transparent Background Entering Current ViewController With Push Transition
  • MongoDb aggregation
  • Custom validator control occupying space even though display set to dynamic
  • JSON response opens as a file, but I can't access it with JavaScript
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Is there a javascript serializer for JSON.Net?
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Read text file and split every line in MSBuild
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • How to add date and time under each post in guestbook in google app engine
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Where to put my custom functions in Wordpress?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Buffer size for converting unsigned long to string
  • WPF Applying a trigger on binding failure
  • coudnt use logback because of log4j
  • embed rChart in Markdown
  • apache spark aggregate function using min value
  • Binding checkboxes to object values in AngularJs