Solved

Insert into Table from Another Table with Conditions

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 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