ОБНОВЛЕНИЕ MySQL - MAX () GROUP BY

Question

ОБНОВЛЕНИЕ MySQL - MAX () GROUP BY

0

Как мне изменить:

SELECT user.postcode, max(postcode.postcode) as postcode
FROM user
INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" )
GROUP BY user.postcode

в UPDATE, подобный?

UPDATE user 
INNER JOIN postcode ON user.postcode LIKE CONCAT(postcode.postcode, "%") 
SET user.lat = postcode.lat, user.lng = postcode.lng

Я не могу понять, как влиять на MAX() и GROUP BY

Ответ

Ниже приведена моя версия кода @ceteras. Он отлично работает!

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp AS
SELECT user.user_id, max(postcode.postcode) AS postcode, postcode.lat, postcode.lng
FROM user
INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" )
GROUP BY user.postcode;
ALTER TABLE tmp ADD PRIMARY KEY(user_id);

UPDATE user 
INNER JOIN tmp ON user.user_id = tmp.user_id
SET user.lat1 = tmp.lat, user.lng1 = tmp.lng;

DROP TABLE tmp;

Simon 16 авг. 2010, в 15:51

Источник

0

Почему вы используете LIKE CONCAT(postcode.postcode, "%") ?
wimvds 16 авг. 2010, в 14:31

Теги:

mysql

sql

group-by

1 ответ

Ещё вопросы

Почему вы используете LIKE CONCAT(postcode.postcode, "%") ?

ceteras · Accepted Answer · 2010-08-16T11-29-00.000Z

Это одноразовая работа? Если да, попробуйте следующее:

drop table if exists tmp;
create table tmp as 
SELECT user.postcode, max(postcode.postcode) as post_code
FROM user
INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" )
GROUP BY user.postcode;
alter table tmp add unique key(postcode, post_code), add key (post_code, postcode);

UPDATE user 
INNER JOIN tmp ON user.postcode = tmp.postcode
inner join postcode p on p.postcode = tmp.post_code
SET user.lat = p.lat, user.lng = p.lng;

drop table tmp;