insert into table taking info from another

Question

in db i have 2 tables "npc" and "droplist"

CREATE TABLE `npc`(  
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `idTemplate` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `name` varchar(200) NOT NULL DEFAULT '',  
  `serverSideName` tinyint(1) NOT NULL DEFAULT '0',  
  `title` varchar(45) NOT NULL DEFAULT '',  
  `serverSideTitle` tinyint(1) NOT NULL DEFAULT '0',  
  `class` varchar(200) DEFAULT NULL,  
  `collision_radius` decimal(6,2) DEFAULT NULL,  
  `collision_height` decimal(6,2) DEFAULT NULL,  
  `level` tinyint(2) DEFAULT NULL,  
  `sex` enum('etc','female','male') NOT NULL DEFAULT 'etc',  
  `type` varchar(22) DEFAULT NULL,  
  `attackrange` smallint(4) DEFAULT NULL,  
  `hp` decimal(30,15) DEFAULT NULL,  
  `mp` decimal(30,15) DEFAULT NULL,  
  `hpreg` decimal(30,15) DEFAULT NULL,  
  `mpreg` decimal(30,15) DEFAULT NULL,  
  `str` tinyint(2) NOT NULL DEFAULT '40',  
  `con` tinyint(2) NOT NULL DEFAULT '43',  
  `dex` tinyint(2) NOT NULL DEFAULT '30',  
  `int` tinyint(2) NOT NULL DEFAULT '21',  
  `wit` tinyint(2) NOT NULL DEFAULT '20',  
  `men` tinyint(2) NOT NULL DEFAULT '20',  
  `exp` int(9) NOT NULL DEFAULT '0',  
  `sp` int(9) NOT NULL DEFAULT '0',  
  `patk` decimal(12,5) DEFAULT NULL,  
  `pdef` decimal(12,5) DEFAULT NULL,  
  `matk` decimal(12,5) DEFAULT NULL,  
  `mdef` decimal(12,5) DEFAULT NULL,  
  `atkspd` smallint(4) NOT NULL DEFAULT '230',  
  `critical` tinyint(1) NOT NULL DEFAULT '1',  
  `aggro` smallint(4) NOT NULL DEFAULT '0',  
  `matkspd` smallint(4) NOT NULL DEFAULT '333',  
  `rhand` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `lhand` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `enchant` tinyint(1) NOT NULL DEFAULT '0',  
  `walkspd` decimal(10,5) NOT NULL DEFAULT '60',  
  `runspd` decimal(10,5) NOT NULL DEFAULT '120',  
  `targetable` tinyint(1) NOT NULL DEFAULT '1',  
  `show_name` tinyint(1) NOT NULL DEFAULT '1',  
  `dropHerbGroup` tinyint(1) NOT NULL DEFAULT '0',  
  `basestats` tinyint(1) NOT NULL DEFAULT '0',  
  PRIMARY KEY (`id`,`idTemplate`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `droplist` (  
  `mobId` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',  
  `min` int(8) unsigned NOT NULL DEFAULT '0',  
  `max` int(8) unsigned NOT NULL DEFAULT '0',  
  `category` smallint(3) NOT NULL DEFAULT '0',  
  `chance` mediumint(7) unsigned NOT NULL DEFAULT '0',  
  PRIMARY KEY (`mobId`,`itemId`,`category`),  
  KEY `key_mobId` (`mobId`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

how to insert into 'droplist' --> 'mobid' 'itemid' 'min' 'max' 'category' 'chance' where "droplist.mobid=npc.id" and from npc.type=L2Raidboss and npc.level >= 76;

INSERT ('' '6673' '10' '15' '1' '1000000') INTO droplist WHERE mobid IN (SELECT id FROM npc WHERE type= 'L2RaidBoss' AND level>= '76');

for sure this dont work but need something like this

If there is any relationship key you can use join and insert the records. Can you provide me the details how these two tables have the relation between each other — backtrack, Oct 13 '14 at 05:02
Not exactly sure what you meant by "*...insert into `droplist`... where droplist.mobid=npc.id*...", but you can try look into the [`INSERT..SELECT`](http://dev.mysql.com/doc/refman/5.7/en/insert-select.html) statement. — ivan.sim, Oct 13 '14 at 05:05
npc.id = droplist.mobid if iam not wrong is a relationship key. — Damian Jan, Oct 13 '14 at 05:33

Kelvin Barsana · Answer 1 · 2014-10-13T06:34:20.290

0

use select query to see if mobid is present in the npc table together with the requirements such as (type=L2Raidboss and level >= 76)

if select query returns a result, proceed with the insert process for droplist table

added:

in case the values for each rows will be the same/constant,

do the first query:

SELECT id FROM npc WHERE type = 'L2RaidBoss' AND level >= '76'

store the results in an array, proceed with the second query

while($array_results[id] ....)//depending on language that you will be using
{
insert into droplist(mobid,itemid,min,max,category,chance) values ('$array_results[id]','6673','10','15','1','1000000')
}

this depends on the language that you will be using, will you be using php for this?

edited Oct 13 '14 at 06:34

answered Oct 13 '14 at 05:37

Kelvin Barsana

824
12
28

SELECT id FROM npc WHERE type = 'L2RaidBoss' AND level >= '76'; as result i have all 76+ level L2Raidboss. – Damian Jan Oct 13 '14 at 05:58
you can store the ids in an array an insert it in the droplist, btw where will you get the values for these 'itemid' 'min' 'max' 'category' 'chance'? – Kelvin Barsana Oct 13 '14 at 06:07
i want to add same value for 'itemid' 'min' 'max' 'category' 'chance' for exmplae ('6673' '10' '15' '1' '1000000') – Damian Jan Oct 13 '14 at 06:23
will you be using php for this? – Kelvin Barsana Oct 13 '14 at 06:36
I want to put droplist using navicat, in console. Just want to take id from 'npc' what is same as mobid in 'droplist' but only take id where type is 'l2raidboss' and level is 76 and higher. – Damian Jan Oct 13 '14 at 09:47

score 0 · Answer 2 · answered Oct 13 '14 at 06:41

try this :

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

or if you wanted to load from another database you can write database migration scripts like : which will provide MySql Officail site facality to import from a cst,text file. For reference

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

insert into table taking info from another

2 Answers2