mysql - 更新表。 SET语句语法错误？

Question

我需要更多帮助。 OK，我有如下表格:

mysql> SELECT * FROM MODELOS;
+--------+----------+---------+
| ID_MOD | ID_MARCA | MODELO  |
+--------+----------+---------+
|      1 |     NULL | PICASSA |
|      2 |     NULL | C4      |
|      3 |     NULL | C3      |
|      4 |     NULL | C2      |
|      5 |     NULL | MONDEO  |
|      6 |     NULL | S-MAX   |
|      7 |     NULL | CIVIC   |
|      8 |     NULL | ACCORD  |
|      9 |     NULL | CLS     |
|     10 |     NULL | 900     |
|     11 |     NULL | LEON    |
|     12 |     NULL | IBIZA   |
|     13 |     NULL | 307     |
|     14 |     NULL | 308     |
|     15 |     NULL | 407     |
|     16 |     NULL | 408     |
|     17 |     NULL | MEGANE  |
|     18 |     NULL | PASSAT  |
|     19 |     NULL | GOLF    |
|     20 |     NULL | TOUAREG |
+--------+----------+---------+

还有一个，FILE，看起来像这样:

+----------+------------+---------+-----------+--------+
| ID_MARCA | MARCA      | MODELO  | MATRICULA | PRECIO |
+----------+------------+---------+-----------+--------+
|        1 | CITROEN    | PICASSA | CPG-2044  |  12000 |
|        1 | CITROEN    | PICASSA | CPR-1762  |  12500 |
|        1 | CITROEN    | C4      | FPP-1464  |  13500 |
|        1 | CITROEN    | C4      | FDR-4563  |  13000 |
|        1 | CITROEN    | C3      | BDF-8856  |   8000 |
|        1 | CITROEN    | C3      | BPZ-7878  |   7500 |
|        1 | CITROEN    | C2      | CDR-1515  |   5000 |
|        1 | CITROEN    | C2      | BCC-3434  |   4500 |
|        2 | FORD       | MONDEO  | BTG-3267  |   8000 |
|        2 | FORD       | MONDEO  | BPP-6792  |   8200 |
|        2 | FORD       | S-MAX   | FDR-1564  |  20000 |
|        2 | FORD       | S-MAX   | FCE-9327  |  21000 |
|        3 | HONDA      | CIVIC   | FCC-7764  |  16000 |
|        3 | HONDA      | CIVIC   | FBC-4567  |  14000 |
|        3 | HONDA      | ACCORD  | FFC-6768  |  22000 |
|        3 | HONDA      | ACCORD  | FPB-2231  |  23000 |
|        4 | MERCEDES   | CLS     | FDR-2265  |  31000 |
|        5 | SAAB       | 900     | FPG-1165  |  21000 |
|        6 | SEAT       | LEON    | DVB-1119  |  14500 |
|        6 | SEAT       | LEON    | DCR-5634  |  13500 |
|        6 | SEAT       | IBIZA   | DPR-3434  |   9500 |
|        6 | SEAT       | IBIZA   | DPP-8756  |  10000 |
|        7 | PEUGEOT    | 307     | DGX-4598  |   5500 |
|        7 | PEUGEOT    | 307     | DGG-2098  |   5000 |
|        7 | PEUGEOT    | 308     | DPK-3859  |   5500 |
|        7 | PEUGEOT    | 308     | DVK-4987  |   6000 |
|        7 | PEUGEOT    | 407     | FFK-1513  |   7500 |
|        7 | PEUGEOT    | 407     | FPP-3768  |   8000 |
|        7 | PEUGEOT    | 408     | CPG-7879  |  10000 |
|        7 | PEUGEOT    | 408     | CRC-9738  |   9500 |
|        8 | RENAULT    | MEGANE  | CQC-4298  |  11000 |
|        8 | RENAULT    | MEGANE  | FNZ-7688  |   6000 |
|        9 | VOLKSWAGEN | PASSAT  | FNR-3987  |  16000 |
|        9 | VOLKSWAGEN | PASSAT  | FQK-3987  |  18000 |
|        9 | VOLKSWAGEN | GOLF    | FCJ-0909  |  22500 |
|        9 | VOLKSWAGEN | GOLF    | FCK-3989  |  21000 |
|        9 | VOLKSWAGEN | TOUAREG | DGG-0937  |  42000 |
+----------+------------+---------+-----------+--------+

我想做的是更新表 MODELOS 中的 ID_MARCA 列，使 ID_MARCA 与列 MODELO 中的字段相对应。例如，在表 MODELOS 中我有一个字段 PICASSA，我需要列 ID_MARCA 中的相应字段的值为 1，因为它是 CITROEN 和 PICASSA 的 ID，如果您查看表 FILE 是 CITROEN。所以，我得到了这个更新声明:

UPDATE TABLE MODELOS
 SET ID_MARCA = (SELECT ID_MARCA FROM FILE)
 WHERE FILE.MARCA IN (SELECT MARCA FFROM MODELOS);

但是这个语句返回 SQL 语法错误。

Answer 1

我建议您不要使用子查询进行更新，如果出于任何原因，直到 5.7 的 MySQL 无法在子查询内进行优化。

来自文档:( https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html )

Note

A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semi-join or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.

我会使用联接:

UPDATE TABLE MODELOS m
  JOIN FILE f ON m.MARCA = f.MARCA
  SET m.ID_MARCA = f.ID_MARCA