Re: Insert Select query problem
Thanks Jay,
I had to make a change to the first part of the query to get the results that I
wanted but your suggestion was definitely what I needed to get to the solution.
Thanks again.
For those that are interested, here's the final solution,
INSERT INTO purchase (Source, Item, Qty)
SELECT 1, totals.Item,if((totals.TotQty -r.Qty)>0,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
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;
>>> On 8/10/07 at 12:33 PM, in message <[EMAIL PROTECTED]>, Jay Pipes <[EMAIL
>>> PROTECTED]> wrote:
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
Re: Insert Select query problem
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Select query problem
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?
Thanks
Re: Insert select query problem
Luis Lebron <[EMAIL PROTECTED]> wrote:
>
> I am try to copy a couple of fields from one table to another. Here is my
> query
>
> Insert into users_test (firstName, lastName) Select firstName, lastName from
> users;
>
> The users table has 1,263 rows. However, only 1 row is inserted into
> users_test.
>
> If I perform the following query
>
> Insert into users_test Select * users;
>
> all the rows are inserted. What am I doing wrong in the first query?
>
Worked perfect for me:
mysql> create table i1(
-> firstname varchar(10),
-> lastname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> create table i2(
-> firstname varchar(10),
-> lastname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into i1 values('aaa','aaa'),('bbb','bbb'),('ccc','ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into i2(firstname, lastname) select firstname, lastname from i1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from i2;
+---+--+
| firstname | lastname |
+---+--+
| aaa | aaa |
| bbb | bbb |
| ccc | ccc |
+---+--+
3 rows in set (0.00 sec)
mysql> truncate i2;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into i2 select * from i1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from i2;
+---+--+
| firstname | lastname |
+---+--+
| aaa | aaa |
| bbb | bbb |
| ccc | ccc |
+---+--+
3 rows in set (0.00 sec)
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert select query problem
I am try to copy a couple of fields from one table to another. Here is my query Insert into users_test (firstName, lastName) Select firstName, lastName from users; The users table has 1,263 rows. However, only 1 row is inserted into users_test. If I perform the following query Insert into users_test Select * users; all the rows are inserted. What am I doing wrong in the first query? thanks, Luis R. Lebron Sigmatech, Inc
