Solved

Insert into Table from Another Table with Conditions

Posted on 2011-03-04
1
346 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

11 Experts available now in Live!

Get 1:1 Help Now