[RESOLVED] deleting records in a table based on a match of two columnns from a subquery

sneakyimp

I have an oscommerce database I'm working with. I want to delete records in the customers_wishlist table which correspond to products that a customer has already purchased. This query returns one record for each product purchase by a customer:

SELECT c.customers_id, op.products_id
FROM customers c
INNER JOIN orders o ON o.customers_id = c.customers_id
INNER JOIN orders_products op ON op.orders_id = o.orders_id

The result is a collection of records with a customer id and and product id. I want to remove all records from another table, customers_wishlist, where the customer id and product id in customers_wishlist are in the results from that first query.

I've been reading the mysql docs on subqueries here:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

But I have not been able to find any way to compare multiple operands to multiple columns.

Will I have to write a PHP script to do this or is it possible in pure sql?

johanafm

First off I'm not certain there is any use to postponing wishlist removal. And even if you don't do that on purchase, but rather on delivery, it should be done at the same time you update orders_products, which makes your select unneccesary. Whether you remove from wishlist when you insert ordered items or update them as shipped, you will have item ids to use.

But I'll answer your specific question since the SQL statement will be the same in the end.

$ids = array();
if ($result = mysql_query($query_from_original_post)) {
	while($row = mysql_fetch_assoc($result))
		$ids[] = $row['products_id'];
	$id_string = implode(',', $ids);
	$delete = 'DELETE FROM wishlist_items WHERE products_id IN (' . $id_string . ') AND customer_id = ' . $customer_id;
	mysql_query($delete);
}

Also, it could be useful to update status rather than removing them. Some items might still be of interest even after you've bought one, if you want to have 5 for example.

sneakyimp

I appreciate the effort you've taken to write some code for me, but it doesn't solve my problem.

More specifically, I'm migrating an ugly non-oscommerce database into an oscommerce database and there are many wishlist items in this legacy database that the associated customers have already bought. the client has asked me to remove any items from a wishlist that a customer has already purchased.

There is no current customer id to use (i'm doing this for all customers at once). I was asking if there is some single query that might do all this at once. I'm capable of writing the PHP code to do it, but was hoping to avoid a PHP step if there's an SQL one that can accomplish the same thing.

johanafm

You can probably do it with procedures, but the simplest approach to me would still be a SQL/PHP mix.
And just to be clear, in the first post you say that you have customer_id and products_ids, which is in line with the query you posted, as opposed to your last post where you say that you don't have customer_id. If you simply ment that you only have it in the tables from which you pull data, I'm with you. Because you will indeed need a combination of customer_id and products_id to remove the correct entries from the wishlist.

$qry = 'SELECT c.customers_id, op.products_id
FROM customers c
INNER JOIN orders o ON o.customers_id = c.customers_id
INNER JOIN orders_products op ON op.orders_id = o.orders_id
ORDER BY customer_id, products_id';

$result = mysql_query($qry);
while($row = mysql_fetch_assoc($result))
	$customers[$row['customer_id']][] = $row['products_id'];

foreach($customers as $customer => $items) {
	$item_str = implode(',', $items);
	/* since this is untested code, I'd start by dumping output to screen or file
	    for a couple of entries and double check... */
	mysql_query('DELETE FROM customers_wishlist WHERE products_id IN ('.$item_str.') AND customer_id = ' . $customer);
}

And you could of course use mysqli and a prepared statement for the delete and just change the parameters. Do test the above, but if I've understood you correctly, it should indeed solve your problem.

sneakyimp

What I said in my last post was that there is no current customer id to use. The query from my first post would retrieve all customers_id/products_id associations for all orders in the db.

I appreciate the effort, and yes that code will do what I want, but you really don't have to write the PHP for me. I was really hoping to discover if there is some PURE SQL solution for this problem -- short of writing some kind of stored procedure or something. It seems like a fairly simple thing, I just haven't been able to find it in the mysql docs.

johanafm

The documentation for CREATE PROCEDURE is under Data Definition Statements (12.1.15 for MySQL 5.1). Additional useful information for loops and cursors is found in section 12.8 MySQL Compound-Statement Syntax.
One major thing to remember is changing the delimiter before creating the procedure and resetting it afterwards. Apart from that it's probably rather self explanatory.
I also altered your SQL statement a little bit and dropped the "FROM customer" part, since it contains no additional information when INNER JOINED with orders. Either the customer_id is in the orders table, or it won't be fetched from customer anyway.

DELIMITER #
CREATE PROCEDURE tidy_wish()
BEGIN
	DECLARE finished INT DEFAULT 0;
	DECLARE cust INT;
	DECLARE prod INT;
	DECLARE cur CURSOR FOR SELECT o.customers_id, op.products_id
FROM orders o
INNER JOIN orders_products op ON op.orders_id = o.orders_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur;

lo: LOOP
	FETCH cur INTO cust, prod;
	IF finished = 1 THEN
		LEAVE lo;
	END IF;

	DELETE FROM customer_wishlist WHERE customer_id = cust AND products_id = prod;
END LOOP lo;

CLOSE cur;
END#
DELIMITER ;

But doing this with pure SQL is not possible as far as I can tell. You will need to resort to procedures.

sneakyimp

Holy cow! You are really dedicated. Thanks for that. I know for sure I won't have permissions to create any procedures. Nice title, btw. tidy_wish haha.

Seems kind of lame that you can't use IN for two or more fields instead of just one. Something like

DELETE FROM customers_wishlist cw WHERE cw.customers_id, cw.products_id IN (SELECT fld1, fld2 FROM some_other_join soj);

It makes SQL seem quaint or sad.

johanafm

🙂
I was really tired last night and didn't even notice it was you posting or I would have skipped writing the php code.

Yeah, that would have been useful.

Also, I have come up with a solution. I just created some test tables and it runs allright, but I'll leave it to you to translate it back to your own table names.

-- ao = orders, aop = orders_products, aw = wishlist
-- cid = customer_id, oid = order_id, opid = product_id
CREATE TEMPORARY TABLE atmp
SELECT DISTINCT ao.cid, aop.opid
FROM ao
INNER JOIN aop ON ao.oid = aop.oid
INNER JOIN aw ON aw.cid = ao.cid AND aw.opid = aop.opid

DELETE aw FROM aw
INNER JOIN atmp ON aw.cid = atmp.cid AND aw.opid = atmp.opid

Lars_Berg

Seems kind of lame that you can't use IN for two or more fields instead of just one.

You can. The syntax is

... where (cw.customers_id, cw.products_id) in (select col1,col2 ...

sneakyimp

Ok, didn't realize you could do a join in a delete statement. That's new. Thanks again johanafm.

Also, thanks Lars! I had tried it before without the parentheses around cw.customers_id and cw.products_id and got a syntax error. I feel silly for not trying harder now.

I think this finds the records I want...it's trivial to make it into a delete statement.

SELECT * FROM customers_wishlist WHERE (customers_id, products_id) IN (
  SELECT o.customers_id, op.products_id
  FROM orders_products op
  INNER JOIN orders o ON o.orders_id = op.orders_id
)

johanafm

Sweet, I learnt something new which will simplify things a whole lot now and then. "knowing" is equally fatal when debugging and learning new things. I wish I could unset(preconceptions)