0

I have a simple table with basic sales info like NAME, PRICE, STATUS. for each customer interaction they are input into the table with NAME and PRICE. if that customer has cancelled the deal then another record is added with the NAME, negative PRICE, and STATUS = cancelled. Here is an example table

+------+--------+-----------+
| NAME | PRICE  |  STATUS   |
+------+--------+-----------+
| bob  |    100 |           |
| mary |    100 |           |
| mary |   -100 | cancelled |
| andy |    250 |           |
| mary |    250 |           |
+------+--------+-----------+

What I want to do is remove both the positive and negative records for deals that have cancelled so that the resulting table would look like this.

+------+--------+-----------+
| NAME | PRICE  |  STATUS   |
+------+--------+-----------+
| bob  |    100 |           |
| andy |    250 |           |
| mary |    250 |           |
+------+--------+-----------+

Any suggestions on how to do this in SQL?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Holden Greene
  • 13
  • 5

1 Answers1

-1

Join the table with itself to match the related records:

DELETE t1
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.name = t2.name AND t1.price IN (t2.price, -t2.price)
WHERE t2.status = 'canceled'

The join finds rows that have the same name, and the price is either the same price or the negative of the price. The WHERE clause restricts this to matching rows that are canceled.

DEMO

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Im getting a syntax error. does this need to be embedded in another SELECT * statement? Where does t2 come from? Also, could you explain the logic of this approach? – Holden Greene May 27 '17 at 00:13
  • Sorry, the second `AS t1` should be `AS t2`. – Barmar May 27 '17 at 00:16
  • syntax error at or near "FROM". I am doing this in the SQL editor on carto onlines web interface, if that makes any difference – Holden Greene May 27 '17 at 00:24
  • Are you sure you're using MySQL, not some other database? – Barmar May 27 '17 at 00:30
  • not sure exactly what SQL version they are using in Carto. ill see if I can tinker with this and get it to work – Holden Greene May 27 '17 at 00:33
  • I've added a link to a demo that shows that it works in MySQL. I don't know what Carto is. – Barmar May 27 '17 at 00:34
  • Found the Carto documentation, it says they use Postgres, not MySQL. – Barmar May 27 '17 at 00:35
  • I added that to the answer. `t2` is the subset of the table that has `status = canceled`. The `JOIN` finds all the rows that have the same name as those rows, and either the same price (which is the canceled row itself) or the negative of the price (the related rows). Those rows are then deleted. – Barmar May 27 '17 at 00:37
  • See https://stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join for how to do the equivalent thing in PosgreSQL. – Barmar May 27 '17 at 00:38