ibazz — 2010-01-21T02:49:55-05:00 — #1
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.
SET latitude_miles = 69.1*latitude,
longitude_mpd = 69.1*cos(latitude*3.14159265/180);
dan_grossman — 2010-01-21T03:16:16-05:00 — #2
address.latitude_miles = 69.1 * table.latitude,
longitude_mpd = 69.1 * COS(table.latitude * 3.14159265 / 180)
something relates the `address` and `table` tables
This is just an UPDATE query that uses multiple tables right?
ibazz — 2010-01-21T03:17:51-05:00 — #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.
ibazz — 2010-01-21T03:19:18-05:00 — #4
We almost cross-posted Dan, so I 'll study your query for future reference. It seems easier to understand than mine.
dan_grossman — 2010-01-21T03:19:23-05:00 — #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.
r937 — 2010-01-21T06:30:38-05:00 — #6
i don't think you do
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
ibazz — 2010-01-21T14:31:42-05:00 — #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.