Solved

Insert into Table from Another Table with Conditions

Posted on 2011-03-04
1
347 Views
Last Modified: 2012-05-11
I am struggling trying to figure out a way to insert records into a table from another... Currently, I have the following two tables (attached as code)...

The first table contains a list of pets "owned" by a particular person and the second contains items owned by the pet. What I am trying to do is give all the pets 1 of item 10 and 1 of item 11 if the pets type is 10.

I tried this query, but it doesn't work.

INSERT pet_items (person_id, pet_item_id, quantity) SELECT DISTINCT p.person_id, 10,1 FROM pets p JOIN pet_items pi ON pi.person_id = p.person_id WHERE p.type = 10 AND pi.pet_item != 15;

 
CREATE TABLE `pets` (
  `pet_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `pet_name` varchar(30) DEFAULT NULL,
  `pet_type` smallint(6) NOT NULL,
  PRIMARY KEY (`pet_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `pet_items` (
  `person_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` smallint(6) NOT NULL,
  PRIMARY KEY (`person_id`,`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Open in new window

0
Comment
Question by:plecostomus
1 Comment
 
LVL 10

Accepted Solution

by:
APNFSSC earned 500 total points
ID: 35040614
I think you will need to do two queries.

INSERT INTO pet_items (person_id, pet_item_id, quantity) VALUES (((SELECT DISTINCT person_id FROM pets WHERE type = '10'), '10', '1'));
INSERT INTO pet_items (person_id, pet_item_id, quantity) VALUES (((SELECT DISTINCT person_id FROM pets WHERE type = '10'), '11', '1'));

There is no pet_item column in either table so I"m not sure what your trying to do with the value of 15.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now