List:General Discussion« Previous MessageNext Message »
From:Jay Pipes Date:August 10 2007 7:33pm
Subject:Re: Insert Select query problem
View as plain text  
Ed Reed wrote:
> Hi All,
>  
> I have an issue that I need to resolve that is difficult to explain. I
> hope that someone can understand what I*m trying to do and shed some
> light on a solution. Here goes.
>  
> I have three tables, inventory, which is a list of transactions with
> positive and negative values; request, which essentially is a temporary
> table that gets deleted after it*s used here; and purchase, which holds
> the solution as to whether an item is to be purchased or removed from
> inventory,
>  
> CREATE TABLE `inventory` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Item` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1; 
>  
> CREATE TABLE `purchase` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Source` int(11) DEFAULT NULL,
>   `Item` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
>  
> CREATE TABLE `request` (
>   `id` bigint(20) NOT NULL AUTO_INCREMENT,
>   `Required` varchar(100) NOT NULL DEFAULT '',
>   `Qty` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
>  
> My Inventory and Request tables have data in them like this,
>  
> Insert Into `inventory` (Item, Qty)
> Values 
> ('Apples',5),
> ('Bananas',4),
> ('Cherries',6),
> ('Apples',-1),
> ('Bananas',1),
> ('Cherries',-2),
> ('Apples',3),
> ('Bananas',-7),
> ('Cherries',19),
> ('Apples',-5),
> ('Bananas',88),
> ('Cherries',6);
>  
> Insert Into `request` (Required, Qty)
> Values
> ('Apples', 12),
> ('Bananas', 112),
> ('Cherries', 5);
>  
> Now what I*d like to do is create a single Insert Select query that
> creates a record in my purchase table for each of the items in my
> request table based on the number of items available in my inventory.
> But, if there aren't enough items in the inventory to cover the amount
> requested, I need to have a second record for that item in the purchase
> table with the qty difference to another source. So based on the data in
> the inventory my current totals are,
>  
> +----------+----------+
> | Item     | Sum(Qty) |
> +----------+----------+
> | Apples   | 2        |
> | Bananas  | 86       |
> | Cherries | 29       |
> +----------+----------+
>  
> and based on the qty of items in my request I would like to have a
> purchase table that looks like this,
>  
> +--------+----------+-----+
> | Source | Item     | Qty |
> +--------+----------+-----+
> | 1      | Apples   | 2   |
> | 0      | Apples   | 10  |
> | 1      | Bananas  | 86  |
> | 0      | Bananas  | 26  |
> | 1      | Cherries | 5   |
> +--------+----------+-----+
>  
> with a source of 1 meaning pull the items from inventory and a source
> of 0 means purchase them from somewhere else.
>  
> Can anyone help me with this?

Try this:

INSERT INTO purchase (Source, Item, Qty)
SELECT
   1, totals.Item, r.Qty
FROM request r
JOIN (
  SELECT Item, SUM(Qty) AS TotQty
  FROM inventory
  GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty <= totals.TotQty
UNION ALL
SELECT
   0, totals.Item, (r.Qty - totals.TotQty)
FROM request r
JOIN (
  SELECT Item, SUM(Qty) AS TotQty
  FROM inventory
  GROUP BY Item
) AS totals
ON r.Required = totals.Item
WHERE r.Qty > totals.TotQty;

cheers,

Jay
Thread
Insert Select query problemEd Reed10 Aug
  • Re: Insert Select query problemJay Pipes10 Aug
    • Re: Insert Select query problemEd Reed10 Aug
      • Changing Big Big integer ValueStephen Sunderlin11 Aug