Writing an SQL query to query and update the same table

Question

I am not very friendly with SQL, so I need to write a SQL update / delete query on the table. This requirement is indicated below.

Table a:

Col1 (PK) Col2 Col3 Amount

1 Code1 Value1 5

2 Codex 2 Meaning 2 2

3 Code1 VALUE1 3

4 Code3 value3 8

Given the table above, in which there are several rows with the same value for Col2 and Col3. I want to write a query that removes a duplicate combination of Col2 and Col3 and sums up the amount for the resulting record.

The result should look like this:

Col1 (PK) Col2 Col3 Amount

1 Code1 Value1 8

2 Codex 2 Meaning 2 2

4 Code3 value3 8

Answer 1

You will need to do this in two parts, and if you want to ensure data integrity, the two parts must be wrapped in a transaction.

The first part updates the required rows of ' Quantity , the second now removes duplicate rows.

 BEGIN TRANSACTION UPDATE TableA SET Quantity=upd.Quantity FROM TableA a INNER JOIN ( SELECT MIN(Col1) AS Col1, SUM(Quantity) AS Quantity FROM TableA GROUP BY Col2, Col3 ) upd ON a.Col1 = upd.col1 ;WITH DELETES AS ( SELECT Col1,ROW_NUMBER() OVER (PARTITION BY Col2,Col3 ORDER BY Col1) rn FROM TableA ) DELETE FROM TableA WHERE Col1 IN ( SELECT Col1 FROM DELETES WHERE rn>1 ) COMMIT TRANSACTION 

Real-time example: http://www.sqlfiddle.com/#!3/9efa9/7

(EDIT: Updated to fix the issue noted in the comments)

Answer 2

Use this:

  select * from (select *, sum(Quantity) over (partition by col2,col3) as Quantity from tableA ) t 

Answer 3

One option is to simply SELECT result set that you want to add to the new table, and then release the previous table:

 CREATE TABLE A_new(Col1 INT PRIMARY KEY, Col2 varchar(255), Col3 varchar(255), Quantity INT); INSERT INTO A_new (Col1, Col2, Col3, Quantity) SELECT MIN(Col1) AS Col1, Col2, Col3, SUM(Quantity) AS Quantity FROM A GROUP BY Col2, Col3 

Then you can drop table A and rename A_new to A :

 DROP TABLE A sp_rename A_New, A 

Answer 4

For the first update step (suppose table A is named Table_1):

 Update Table_1 set Quantity = t.total from Table_1 As p inner join (select Min(Col1) as Col1,SUM(quantity) as total from Table_1 group by Col2,Col3) as t on p.Col1=t.Col1 

this will update each row with more than one row with a SUM quantity.

then you can delete the same line whose code2 has the same value:

 WITH CTE AS( SELECT RN = ROW_NUMBER()OVER(PARTITION BY Col2,Col3 ORDER BY Col2,Col3) FROM Table_1 ) DELETE FROM CTE WHERE RN > 1; 

Sorry, I thought Col2 will always be the same with Col3. * I edited my statements. If you get more than 1 line, this can delete everything except the first line.

Answer 5

Please complete the following query:

 SELECT Col2, Col3, SUM(Quantity) FROM table_1 GROUP BY Col2, Col3