CODEWITHSUNDEEP
sqlsql-server
SLDem
SLDem

Reputation: 2182

How to copy SQL table data without duplicates?

I have a table called MovementEntry

Sample Data:

Id Quantity Price EntrySort Product Movement
1 80 8$ Light Beer 1
2 70 9$ Dark Lager 1
3 40 10$ Dark Ale 2
4 55 7$ Red Vine 2

Sample Output after running the query (below):

Id Quantity Price EntrySort Product Movement
1 80 8$ Light Beer 1
2 70 9$ Dark Lager 1
3 40 10$ Dark Ale 2
4 55 7$ Red Vine 2
5 80 8$ Light Beer 2
6 70 9$ Dark Lager 2

When I run it a second time it shouldn't do anything since all the Products are already in the table.

This is the query that is supposed to copy all the rows with minimum Movement value into the same table but with the maximum Movement value only without duplicate Products.

INSERT INTO MovementEntry (Quantity, Price, EntrySort, Product, Movement)
(SELECT Quantity, Price, EntrySort, Product, (SELECT MAX(IncOutc) FROM Movement) 
FROM MovementEntry 
WHERE Movement = (SELECT MIN(IncOutc) FROM Movement))
AND NOT EXISTS(SELECT Product FROM MovementEntry)

But it gives me an error:

Incorrect syntax near the keyword 'AND'.

On line 5.

UPDATE

Movement Table Structure (as requested)

Id Added At IncomeOutcome
1 2021-07-02 1
2 2021-09-02 2

(Here in the IncomeOutcome filed 1 means that Products were received and 2 means that they were sold)

Upvotes: 0

Views: 81

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Please try now.

  with cte as(
    SELECT Quantity, Price, EntrySort, Product, (SELECT MAX(incomeoutcome) FROM Movement)Movement,ROW_NUMBER()over(partition by product order by movement) rn
    FROM MovementEntry 
    WHERE Movement = (SELECT MIN(incomeoutcome) FROM Movement) 
    ) 
    INSERT INTO MovementEntry (Quantity, Price, EntrySort, Product, Movement)
    select  Quantity, Price, EntrySort, Product, Movement from cte where rn=1
    and not exists (select * from movemententry m where m.product=cte.product and m.movement=cte.Movement)

Upvotes: 1

Related Questions