Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert into Table from Another Table with Conditions

Posted on 2011-03-04
1
Medium Priority
?
365 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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

876 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