Mysql – How to connect use INSERT query using SELECT query in theSQL

insertMySQLselect

I have 2 tables, cce_sa_fa & cce_activity

I used the following line to get the cce_sa_fa not included in cce_activity

select id from cce_sa_fa where id NOT IN (select safa from cce_activity)

Using this query, I got 113 rows. Now I need to INSERT these ids into cce_activity. Here the id needs to be inserted and other columns are with fixed value. Usual insert query for cce_activity is as follows.

INSERT INTO `cce_activity` (`id`, `safa`, `name`, `mark`) VALUES (NULL, '"+id_of_ cce_sa_fa+"', 'Activity 1', '0');

How can I insert all the ids from the SELECT query to INSERT into cce_activity

Best Answer

if columns same:

INSERT INTO cce_activity select * from cce_sa_fa where id NOT IN (select safa from cce_activity)

if not, something like:

INSERT INTO cce_activity (col1, col2, col3) select col1, col2, col3 from cce_sa_fa where id NOT IN (select safa from cce_activity)

Related Solutions

MySQL Insert into two tables using new IDs

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
    ( relid, price, ... )          --- relid and all the other columns, 
                                   --- except any autoincrement you may have
SELECT pasted.id, p.price, ....    --- and the same columns here
FROM 
    pricing AS p
  JOIN
    tblproductconfigoptionssub AS copied
      ON  copied.id = p.relid
      AND copied.configid = 41 
  JOIN
    tblproductconfigoptionssub AS pasted
      ON  pasted.optioname = copied.optioname
      AND pasted.configid = 76 ;

MySQL – How to replace a given value from multiple columns when using SELECT

SELECT IF(A01 = 99999, NULL, A01) AS A01,
       IF(A02 = 99999, NULL, A02) AS A02,
       ...
    FROM ...

(For empty string, use '' instead of NULL.)

Best Answer

Related Solutions

MySQL Insert into two tables using new IDs

MySQL – How to replace a given value from multiple columns when using SELECT

Related Question