#1064 - You have an error in your SQL syntax... near FROM

Question

I'm quite new MySQL but have had some success until hitting this roadblock. Any help would be much appreciated.

I am attempting to update Table 1 based on a matching column in Table 2. MySQL version is 5.5. Here's the 2 tables (apologies for the similar table/column names, should have planned the column naming beforehand):

Table 1 - domains

+--------+------------------------------------------+----------------+-----------+
| ID     | DomainKeyword                            | GoogleSearches | GoogleCPC |
+--------+------------------------------------------+----------------+-----------+
| 406499 | 1k commandments                          |           NULL |      NULL |
| 406500 | 1k feet deep                             |           NULL |      NULL |
| 406501 | 1 market square                          |           NULL |      NULL |
| 406502 | 1marketst 3012                           |           NULL |      NULL |
| 406503 | 1 massive cashflow                       |           NULL |      NULL |
| 406504 | 1 min tv                                 |           NULL |      NULL |
| 406505 | 1 minutes tv                             |           NULL |      NULL |
| 406506 | 1 minutes tv                             |           NULL |      NULL |
| 406507 | 1 money mentor                           |           NULL |      NULL |
| 406508 | 1 my bia2 music                          |           NULL |      NULL |
| 406509 | 1n4j8                                    |           NULL |      NULL |

Table 2 - googlesearches

+------+----------------------------------+----------+-------+
| ID   | Keyword                          | Searches | CPC   |
+------+----------------------------------+----------+-------+
| 3330 | audio buss                       |      480 |  0.00 |
| 3331 | balls to poverty                 |       30 |  0.00 |
| 3332 | boa homes                        |       10 |  0.00 |
| 3333 | bath construction                |       10 |  0.00 |
| 3334 | bread crumbs catering            |       10 |  0.00 |
| 3335 | complete recruit                 |       90 |  0.00 |
| 3336 | all about carpets                |       10 |  0.00 |
| 3337 | consulting world                 |       10 |  0.00 |
| 3338 | car insurance loans              |       50 |  3.64 |
| 3339 | building experts                 |       50 |  0.00 |
| 3340 | boyfriend jealousy               |       30 |  0.00 |
| 3341 | avid farm shop                   |       30 |  0.00 |
| 3342 | chic bridesmaid                  |       10 |  0.00 |
| 3343 | ad wholesale                     |       10 |  0.00 |
| 3344 | buy game card                    |       10 |  0.00 |
| 3345 | daily driving                    |       10 |  0.00 |
| 3346 | church farm cottage              |      260 |  1.18 |

Here is the command, it seems certain commands I run with a 'FROM' throw the same error:

UPDATE dest
  SET GoogleSearches = src.Searches,
    GoogleCPC = src.CPC
FROM domains AS dest
INNER JOIN googlesearches AS src
ON dest.DomainKeyword = src.Keyword;

The error I am getting when attempting to run the command:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM domains AS dest INNER JOIN googlesearches AS src ON dest.DomainKeyword = ' at line 4

If I am not mistaken, a MySQL `UPDATE` query with multiple tables would look as follows: `UPDATE domains, googlesearches SET GoogleSearches = src.Searches, GoogleCPC = src.CPC WHERE domains.DomainKeyword = googlesearches.Keyword;`. I'll move this to an answer should it work. — Jonathon Ogden, Jul 12 '16 at 15:53
Possible duplicate of [How to use Inner Join in Update statement In MYSQL](http://stackoverflow.com/questions/8057565/how-to-use-inner-join-in-update-statement-in-mysql) — Drew, Jul 12 '16 at 15:56
Try this query UPDATE domains dos, googlesearches gs SET dos.GoogleSearches = gs.Searches WHERE dos.DomainKeyword = gs.Keyword; — Rakesh Kumar, Jul 12 '16 at 15:56

score 0 · Accepted Answer · answered Jul 12 '16 at 15:53

0

try this: reverse your query...I think it's reversed in mysql than in sql server:

UPDATE domains AS dest
INNER JOIN googlesearches AS src ON dest.DomainKeyword = src.Keyword
  SET dest.GoogleSearches = src.Searches,
   dest.GoogleCPC = src.CPC;

answered Jul 12 '16 at 15:53

arcee123

101
9
41
118

Perfect, first ever post on stackoverflow solved in 2 minutes! :) – Hungry Jul 12 '16 at 15:59

score 0 · Answer 2 · answered Jul 12 '16 at 15:55

0

Try This

UPDATE domains dos, googlesearches gs SET dos.GoogleSearches = gs.Searches WHERE dos.DomainKeyword = gs.Keyword;

answered Jul 12 '16 at 15:55

Rakesh Kumar

4,319
2
17
30

#1064 - You have an error in your SQL syntax... near FROM

2 Answers2