CNB CNB - 2 months ago 9
MySQL Question

UPDATE table. SET statement wrong syntax?

I need some more help. OK, I've got a table as follows:

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 |
+--------+----------+---------+


and another one, FILE, which looks like this:

+----------+------------+---------+-----------+--------+
| 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 |
+----------+------------+---------+-----------+--------+


What I am trying to do is to update column ID_MARCA in table MODELOS in a way that ID_MARCA corresponds with field in column MODELO. For example, in table MODELOS I have a field PICASSA, and I need corresponding field in column ID_MARCA to be of value 1 as it is ID of CITROEN and PICASSA, if you look into a table FILE is a CITROEN.
So, I've got this update statement:

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


but this statement returns SQL syntax error.

Answer

First, note that there are multiple rows for a given file with a given marca. You probably don't want to do multiple updates.

Second, I don't readily detect a syntax error in your query. I do detect two runtime errors. The first is that (in MySQL) you can't reference the table being updated (this might be a compile-time error). Second, your subqueries are returning multiple rows.

If you want to follow your path of using a subquery, then use a correlated subquery:

UPDATE TABLE MODELOS m
  SET m.ID_MARCA = (SELECT f.ID_MARCA
                    FROM FILE f
                    WHERE f.MARCA = m.MARCA
                    LIMIT 1
                   );
Comments