mysql - MySQL 5.5 中多表更新的正确语法

Question

我正在尝试验证在 MySQL 5.5 中执行多表更新所使用的正确语法。我有两个表，第一个表的结构如下:

| *post_id* | lat | long |

其中 post_id 是唯一标识符。第二个表是 WordPress postmeta 表:

| *meta_id* | post_id | meta_key | meta_value |

其中 meta_id 是唯一标识符。第一个表中的 Post_ID 与第二个表匹配。第二个表中的一些 meta_keys 包括 list-lat 和 list-long。

我希望使用第二个表中的相应值更新第一个表中的 long 值，其中 long 为 0.000000，lat不是 0.000000。以下 select 语句标识 130 行:

SELECT * FROM bch_postmeta
JOIN bch_coords ON bch_coords.post_id = bch_postmeta.post_id
WHERE bch_coords.long = 0.000000 
AND bch_coords.lat <> 0.000000 AND bch_postmeta.meta_key = 'list-long'

这符合预期。如果我尝试一个简单的 UPDATE 并模拟它，我将再次影响 130 行:

UPDATE bch_coords
SET bch_coords.long = 0.0 
WHERE bch_coords.long = 0.000000 AND bch_coords.lat <> 0.000000

但是，一旦我尝试合并第二个表，查询就停止工作。以下两个查询均影响 0 行。第二个当然是我希望进行的更新。

UPDATE bch_coords, bch_postmeta
SET bch_coords.long = 0.0 
WHERE bch_coords.long = 0.000000 AND bch_coords.lat <> 0.000000

UPDATE bch_coords, bch_postmeta
SET bch_coords.long = bch_postmeta.meta_value
WHERE bch_coords.post_id = bch_postmeta.post_id AND bch_postmeta.meta_key = 'list-long'
AND bch_coords.long = 0.000000 AND bch_coords.lat <> 0.000000

我没有使用ANSI连接，因为它们在UPDATE中使用时由于某种原因(此时也是一个谜)而导致查询失败。

任何人都可以阐明我在这里做错了什么吗？

我在下面创建了一个 SQLFiddle markdown:

SQL Fiddle

MySQL 5.5 架构设置:

CREATE TABLE bch_coords
    (`post_id` int, `lat` numeric, `long` numeric, UNIQUE(post_id))
;

INSERT INTO bch_coords
    (`post_id`, `lat`, `long`)
VALUES
    (1, 15.986132, 136.82515),
    (2, 0.000000, 0.000000),
    (3, -15.23850, 0.000000),
    (4, 136.32067, 0.000000),
    (5, -87.123567, 56.12396)
;


CREATE TABLE bch_postmeta
    (`meta_id` int, `post_id` int, `meta_key` varchar(13), `meta_value` varchar(15), UNIQUE(meta_id))
;

INSERT INTO bch_postmeta
    (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
    (1, 1, '''list-lat''', '15.986132'),
    (2, 1, '''list-long''', '136.82515'),
    (3, 1, '''country''', '''Switzerland'''),
    (4, 1, '''state''', '''Valais'''),
    (5, 2, '''list-lat''', '0.000000'),
    (6, 2, '''list-long''', '0.000000'),
    (7, 2, '''country''', '''Australia'''),
    (8, 3, '''list-lat''', '-15.00'),
    (9, 3, '''list-long''', '173.62198'),
    (10, 3, '''country''', '''USA'''),
    (11, 4, '''list-lat''', '136.32067'),
    (12, 4, '''list-long''', '5.123997'),
    (13, 5, '''list-lat''', '-88.125'),
    (14, 5, '''list-long''', '56.12396')
;

所以，我有一个如下所示的数据集......

SELECT * FROM bch_coords;
+---------+------+------+
| post_id | lat  | long |
+---------+------+------+
|       1 |   16 |  137 |
|       2 |    0 |    0 |
|       3 |  -15 |    0 |
|       4 |  136 |    0 |
|       5 |  -87 |   56 |
+---------+------+------+

SELECT * FROM bch_postmeta;
+---------+---------+-------------+---------------+
| meta_id | post_id | meta_key    | meta_value    |
+---------+---------+-------------+---------------+
|       1 |       1 | 'list-lat'  | 15.986132     |
|       2 |       1 | 'list-long' | 136.82515     |
|       3 |       1 | 'country'   | 'Switzerland' |
|       4 |       1 | 'state'     | 'Valais'      |
|       5 |       2 | 'list-lat'  | 0.000000      |
|       6 |       2 | 'list-long' | 0.000000      |
|       7 |       2 | 'country'   | 'Australia'   |
|       8 |       3 | 'list-lat'  | -15.00        |
|       9 |       3 | 'list-long' | 173.62198     |
|      10 |       3 | 'country'   | 'USA'         |
|      11 |       4 | 'list-lat'  | 136.32067     |
|      12 |       4 | 'list-long' | 5.123997      |
|      13 |       5 | 'list-lat'  | -88.125       |
|      14 |       5 | 'list-long' | 56.12396      |
+---------+---------+-------------+---------------+

...更新后，我想要一个如下所示的数据集...

SELECT * FROM bch_coords;
+---------+------+------+
| post_id | lat  | long |
+---------+------+------+
|       1 |   16 |  137 |
|       2 |    0 |    0 |
|       3 |  -15 |  173 |
|       4 |  136 |  5.1 |
|       5 |  -87 |   56 |
+---------+------+------+

Answer 1

试试这个:

UPDATE bch_coords
SET bch_coords.long = (SELECT bch_postmeta.meta_value 
                       FROM bch_postmeta 
                       WHERE bch_postmeta.post_id = bch_coords.post_id
                       AND bch_postmeta.meta_key = 'list-long'
                       LIMIT 1)
WHERE bch_coords.long = 0.000000 
AND bch_coords.lat <> 0.000000

或者这个:

UPDATE bch_coords 
INNER JOIN bch_postmeta ON (bch_coords.post_id = bch_postmeta.post_id AND bch_postmeta.meta_key = 'list-long')
SET bch_coords.long = bch_postmeta.meta_value
WHERE bch_coords.long = 0.000000 
AND bch_coords.lat <> 0.000000