MYSQL INSERT query based on SELECT query with LIMIT restrictions

Question

SELECT query

SELECT a. * , d.agent_id, COUNT( d.driver_id ) AS `noofdrivers`
                                      FROM ta_agent a, ta_drivers d
                                      WHERE a.agent_id = d.agent_id
                                      AND a.agent_id = ".$agent_id."
                                      GROUP BY a.agent_id

SELECT query returns this below records

agent_id  agent_name company_name  pricing_plan  noofdrivers
--------  ----------  -----------  ------------  -----------
    3         CCC           ZZZ          8499       2
    2         BBB           YYY          3499       3

DRIVER DB TABLE

agent_id  driver_id   driver_name
--------  ----------  -----------
2         1           EEE
2         2           FFF
2         3           GGG
1         4           HHH
3         5           III
3         6           JJJ

how do I INSERT the records based on above SELECT query results. if pricing_plan is 3499 agent_id 2 can add only 5 drivers. Now, agent_id 2 have 3 drivers. So, still he can add 2 drivers only. How to do this limit restriction in mysql query?

Can you give examples of what you would want to happen when (1) an agent has X empty spaces and inserts X items? — Daniel Renshaw, Apr 23 '14 at 08:27
@MinhD : i don't know about statement level trigger. if you have any solution provide me — Karuppiah RK, Apr 23 '14 at 08:32
@DanielRenshaw : eg: agentid 2 have 3 drivers and agentid 2 pricing plan is 3499. if pricing plan is 3499 agentid 2 can add 5 drivers only. if he add 6th driver it won't insert in database. some alert will shown to user. got it ? — Karuppiah RK, Apr 23 '14 at 08:35
[This may help](http://stackoverflow.com/questions/23223656/oracle-how-to-force-user-to-insert-multiple-row). In this case, the user want to limit a hotel room booking system. Min 3 and max 5 people can book into 1 room. — MinhD, Apr 23 '14 at 08:46

score 1 · Accepted Answer · answered Apr 23 '14 at 09:00

1

Try this,

insert into driver(driver_id, agent_id, driver_name)
   select ins.driver_id, ins.agent_id, ins.driver_name
   from (select 7 as driver_id, 2 as agent_id, 'KKK' as driver_name) ins
   where (select count(*) 
      from driver d, agent a
      where d.agent_id=a.agent_id and a.agent_id=ins.agent_id and a.pricing_plan=3499)<5;

In given query, you'l have to place variables $driver_id, $agent_id and $driver_name for values 7, 2 and 'KKK' respectively.

so, it will look like,

$sql="insert into driver(driver_id, agent_id, driver_name)
   select ins.driver_id, ins.agent_id, ins.driver_name
   from (select $driver_id as driver_id, $agent_id as agent_id, $driver_name as driver_name) ins
   where (select count(*) 
      from driver d, agent a
      where d.agent_id=a.agent_id and a.agent_id=ins.agent_id and a.pricing_plan=3499)<5";

Here is DEMO SQLFiddle

answered Apr 23 '14 at 09:00

Ravi Dhoriya ツ

4,435
8
37
48

how to insert the values into multiple tables with this query `insert into ta_drivers(driver_id, agent_id, driver_name) insert into ta_vehicles(driver_id, vehicle_name, registration_number) select ins.driver_id, ins.agent_id, ins.driver_name, ins.driver_id, ins.vehicle_name, ins.registration_number from (select 7 as driver_id, 4 as agent_id, 'KKK' as driver_name, 'LENOVA' as vehicle_name, 'TN 05' as registration_number) as ins where (select count(*) from ta_drivers d, ta_agent a where d.agent_id=a.agent_id and a.agent_id=ins.agent_id and a.pricing_plan=1799)<5;` – Karuppiah RK Apr 23 '14 at 09:47
This wasn't mentioned before. :P Btw you can't insert to multiple tables using one query, check [this](http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) – Ravi Dhoriya ツ Apr 23 '14 at 09:51

score 0 · Answer 2 · answered Apr 23 '14 at 08:46

You could use something like this:

insert into ta_drivers(agent_id, driver_id, driver_name) SELECT (agent_id, driver_id, driver_name) FROM dual where ((SELECT a. * , d.agent_id, COUNT( d.driver_id ) AS `noofdrivers`
                                  FROM ta_agent a, ta_drivers d
                                  WHERE a.agent_id = d.agent_id
                                  AND pricing_plan=3499 and a.agent_id = ".$agent_id."
                                  GROUP BY a.agent_id)<5);

You have to check the query because is not OK. but in theory:

insert into table select from dual where ((select count(*) condition) <5);

you clould check: http://stackoverflow.com/questions/18584541/mysql-insert-into-with-dual-condition-for-if-not-exist or http://stackoverflow.com/questions/18728058/insert-into-table-using-result-set-of-dual-query — Ardi Goxhaj, Apr 23 '14 at 08:54

MYSQL INSERT query based on SELECT query with LIMIT restrictions

2 Answers2