Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Sum then delete one row - SQL Server 2008 R2
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
CREATE TABLE #temp( Item VARCHAR(max), Qty DECIMAL(18,2), FixItem VARCHAR(max) ); INSERT INTO #temp(Item, Qty, FixItem) VALUES ('ItemRandom-1', 2, 'ItemA'), ('ItemRandom-2', 5, 'ItemA'), ('ItemRandom-3', 5, 'ItemB'), ('ItemRandom-4', 5, ''), ('ItemRandom-5', 5, ''); WITH t AS ( SELECT *, SUM(Qty)OVER(PARTITION BY FixItem) t, ROW_NUMBER()OVER(PARTITION BY FixItem ORDER BY Item) n FROM #temp WHERE FixItem != '' ) MERGE t USING (SELECT * FROM t WHERE n = 1) s ON t.Item = s.Item AND t.FixItem = s.FixItem AND t.n = s.n WHEN MATCHED THEN UPDATE SET t.Qty = s.t WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.*; SELECT * FROM #temp;
View schema
Execution time: 0,05 sec, rows selected: 4, rows affected: 8, absolute service time: 0,19 sec
edit mode
|
history
|
discussion
$action
Item
Qty
FixItem
t
n
1
UPDATE
ItemRandom-1
2,00
ItemA
7,00
1
2
DELETE
ItemRandom-2
5,00
ItemA
7,00
2
3
UPDATE
ItemRandom-3
5,00
ItemB
5,00
1
Item
Qty
FixItem
1
ItemRandom-1
7,00
ItemA
2
ItemRandom-3
5,00
ItemB
3
ItemRandom-4
5,00
4
ItemRandom-5
5,00