Is this do-able?

Databases
1

Hi,

Is it possible to select, mathematise and insert all in one query, or need I do it in my server langauge of choice?

firstly, I need to select and then insert as shown below.


select longitude
       , latitude
  from table

UPDATE address 
     SET latitude_miles = 69.1*latitude, 
         longitude_mpd = 69.1*cos(latitude*3.14159265/180);

bazz

2 
UPDATE 
  address, table
SET
  address.latitude_miles = 69.1 * table.latitude,
  longitude_mpd = 69.1 * COS(table.latitude * 3.14159265 / 180)
WHERE
  something relates the `address` and `table` tables

This is just an UPDATE query that uses multiple tables right?

3

I got it.


 UPDATE address SET latitude_miles = 69.1 * latitude,
longitude_mpd = 69.1 * COS( latitude * 3.14159265 /180 );
SELECT a.latitude, a.longitude
FROM address AS a

I added a ; which I thought would break the query into two but, it worked.

bazz

4

We almost cross-posted Dan, so I 'll study your query for future reference. It seems easier to understand than mine.

Thanks

bazz

5

Are you just asking how to use the MySQL client? Yes a semicolon ends a query and you can type multiple queries at the same time.

6

i don’t think you do :slight_smile:

what you show there is an UPDATE followed by a SELECT, but where does the UPDATE get its values from?

dan’s query is a joined update which allows values from one table to update the values in another table

it isn’t just “easier to understand” but also the only way to do what you’re asking

:slight_smile:

7

Thanks both of you.

Yes, I was asking how to use the MySQL window to do the update where the query had to get the basic values and then manipulate them before updating.

I ran the ‘script’ I posted and whilst it showed two empty cols in phpMyAdmin, it did do the update.

I’ll go back over it again and make sense of Dan’s post.

Thanks again.

bazz