CODEWITHSUNDEEP
mysqlvb.net
user3872350
user3872350

Reputation: 21

SQL UPDATE whilst Iterating

I am a regular VB programmer but new to SQL and struggling with a few things that I need to perform. I am looking for help with an update of specific records in a table as follows.

I have a batch of vouchers in a table which are currently unallocated to a customer (these records have the field assigned_to set to zero) from which I need to allocate 10 of these vouchers from a specific batch of vouchers to a specific set of customers.

The customers have a unique id in the customers table but I only wish to update the customers who have a customer_type of ‘AABB’

The SQL I use to allocate a one-off batch of 10 to a customer who has an id of 4385:

UPDATE vouchers
SET assigned_to_id = '4385'
WHERE batch_id = '9999999999'
AND assigned_to = '0'
LIMIT 10

What I do not know how to do is to create some routine or procedure that will select the appropriate customers and update each on in turn with 10 vouchers from the batch

This is how I would select the customer_id from the customers table

SELECT customer_id from customers 
WHERE customer_type = ‘AABB’

What I am looking for is the actual SQL to make this work.

I realise this next bit is incorrect but this is how I see I require the SQL to work. I need to cycle through the select customers and update the vouchers (10 at a time) to each one.

For each customer_id
    UPDATE vouchers
    SET assigned_to_id = customer_id
    WHERE batch_id = '9999999999'
    AND assigned_to = '0'
    LIMIT 10
Next customer_id

Upvotes: 2

Views: 57

Answers (1)

Kickstart
Kickstart

Reputation: 21513

This is possible to do in a single SQL statement, although it isn't that efficient.

UPDATE vouchers
INNER JOIN 
(
    SELECT voucher_id, @cust_cnt1:=IF(@voucher_cnt < 10, @cust_cnt1, @cust_cnt1 + 1) AS cust_cnt, @voucher_cnt:=IF(@voucher_cnt < 10, @voucher_cnt + 1, 1)
    FROM vouchers
    CROSS JOIN(SELECT @voucher_cnt:=0, @cust_cnt1:=1) sub0
    WHERE batch_id = '9999999999'
    AND assigned_to_id = '0'
) sub1
ON vouchers.voucher_id = sub1.voucher_id
INNER JOIN
(
    SELECT customer_id, @cust_cnt2:=@cust_cnt2 + 1 AS cust_cnt
    FROM customers 
    CROSS JOIN(SELECT @cust_cnt2:=0) sub0
    WHERE customer_type = 'AABB'
) sub2
ON sub1.cust_cnt = sub2.cust_cnt
SET vouchers.assigned_to_id = sub2.customer_id;

This has a sub query against vouchers which returns the voucher_id (or whatever unique key the vouchers table has, and a couple of sequence numbers. First sequence number is the match the one generated later for the customers while the 2nd goes from 1 to 10. When the 2nd reaches 10 the first one is incremented. This was you have batches of 10 vouchers.

The 2nd sub query just returns the customer id with a sequence number calculated.

The 2 sub queries are then joined based on the 1st sequence number from the first sub query and the sequence number from the 2nd sub query. So for any matching customer id there should be 2 vouchers.

This is then joined to the vouchers table (based on voucher_id) and the customer_id used to populate the assigned_to_id.

An SQL fiddle to demonstrate this:-

http://www.sqlfiddle.com/#!2/58aa2a

EDIT - Mysql does support stored procedures (although I am far from experienced with them) which allow you to do processing (loops, etc) as you would in VB.

I have quickly knocked up the following stored procedure which takes 2 parameters, one being the customer type and the other the batch id, finds all the customers of that customer tye and then updates 10 vouchers of the required batch id to have that customer id.

You create the stored procedure as follows (note, you create it once in the database, not each time you want to do it)

DELIMITER //
CREATE PROCEDURE UpdateVouchers(IN in_customer_type VARCHAR(255), IN in_batch_id VARCHAR(255))
BEGIN
    DECLARE not_found_customers INT DEFAULT 0;
    DECLARE fnd_customer_id INT;
    DECLARE csr_customers CURSOR FOR 
    SELECT customer_id from customers WHERE customer_type = in_customer_type;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_customers = 1;
    OPEN csr_customers;
    SET not_found_customers = 0;
    credit_loop : LOOP 
        FETCH csr_customers INTO fnd_customer_id;
        IF not_found_customers THEN
            CLOSE csr_customers;
            LEAVE credit_loop;
        END IF;
        UPDATE vouchers SET assigned_to_id = fnd_customer_id WHERE batch_id = in_batch_id AND assigned_to_id = '0' ORDER BY voucher_id LIMIT 10;
    END LOOP credit_loop;
END

You can then execute it with the following

CALL UpdateVouchers('AABB', '9999999999')

Upvotes: 1

Related Questions