count distinct rows

with DateRowCount(col1,CountRows)
as
(
select col1,COUNT(col1)
over (partition by col1) from table1 as CountRows
)
select col1, CountRows
from DateRowCount
group by col1, CountRows
order by col1

Update table from another table

UPDATE table1
SET
col1 = B.col1
, col2 = B.col2
, col3 = B.col3
from table2 B
where B.col4 = table1.col4 and B.col5 = table1.col5
and exists (select 1 from table2 where table2.account = table1.account and table2.memo_number = table1.memo_number )

Delete Duplicate rows

WITH CTE (account,memo_number, DuplicateCount)


AS

(

SELECT account,memo_number, valdate,

ROW_NUMBER() OVER(PARTITION BY account,memo_number ORDER BY valdate desc) AS DuplicateCount

FROM inv1_transactions_bkp

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO