MYSQL Insert into select filtering fields

Question

I have to insert a new row in table1 for each row found in table2. The problem is that the select in table2 returns more fields than are needed to the insert, but are useful in where clause of select.

This query shows to a user all the shops around, based on their (shops) range (defined in the table stores)

SELECT destination.poi_id,
  6371 * 
     2 * 
     ASIN(
       SQRT(
         POWER(SIN((use_lat - poi_lat) * PI()/180 / 2), 2) +
         COS(use_lat * pi()/180) * 
         COS(poi_lat * pi()/180) *
         POWER(SIN((use_lon - poi_lon) * PI()/180 / 2), 2)
       )
     ) AS distance,
  destination.poi_range AS range
FROM stores destination, users origin
WHERE origin.use_id=userid
  AND destination.poi_lon BETWEEN lon1 AND lon2
  AND destination.poi_lat BETWEEN lat1 AND lat2
  HAVING distance <= range
ORDER BY distance;

Now I have to put these results in a table having this structure

user_id  INTEGER
poi_id   INTEGER
ins_date TIMESTAMP (CURRENT TIMESTAMP)

I don't know how to do it, can you help me?

INSERT INTO table (user_id, poi_id)
SELECT ... ? (too many fields in select)

Giles · Accepted Answer · 2015-05-19T09:52:14.820

1

Reading between the lines a bit but basically only include the columns you need in the insert IE:

INSERT INTO table (user_id, poi_id)
SELECT use_id,poi_id,NOW() FROM
    (SELECT origin.use_id,destination.poi_id,6371 * 
     2 * 
     ASIN(
       SQRT(
         POWER(SIN((use_lat - poi_lat) * PI()/180 / 2), 2) +
         COS(use_lat * pi()/180) * 
         COS(poi_lat * pi()/180) *
         POWER(SIN((use_lon - poi_lon) * PI()/180 / 2), 2)
       )
     ) as distance
    FROM stores destination, users origin
    WHERE origin.use_id=userid
      AND destination.poi_lon BETWEEN lon1 AND lon2
      AND destination.poi_lat BETWEEN lat1 AND lat2
      HAVING distance <= range) sub1
ORDER BY distance;

edited May 19 '15 at 09:52

answered May 19 '15 at 08:53

Giles

1,597
11
15

where I put the distance calculation for the condition and the ordering? – Scream May 19 '15 at 09:00
Sorry, missed the distance thing. I think the easiest here is to use a sub select to do the clauses then select the required columns out of that (the other alternative would be to put that long calc in both the HAVING and ORDER clauses). See my modified post above. – Giles May 19 '15 at 09:55

score 0 · Answer 2 · answered May 19 '15 at 08:58

You just need to add in your select user_id and remove distance and range because there are not in your table and actually you have to add ins_date because you dont define VALUE statement in your Insert. So your Select should be something like this

INSERT INTO table values (user_id, poi_id) 
   Select user_id, poi_id from FROM stores destination, users origin
WHERE origin.use_id=userid
  AND destination.poi_lon BETWEEN lon1 AND lon2
  AND destination.poi_lat BETWEEN lat1 AND lat2
  HAVING distance <= range
ORDER BY distance;

if you need to add date in your table include this field within brackets and into select statement too

yes you are right, and iclude distance field in your select and within brackets — The Reason, May 19 '15 at 09:56

MYSQL Insert into select filtering fields

2 Answers2