How to fix syntax error where FROM is incorrect when joining two tables?

Question

I'm developing a database and I using 2 specific tables in this problem, being Artwork (ArtworkID, Price, PurchaseID) and Purchase (PurchaseID, Total)

I want to add together the Artwork.Price of the artworks that have the same Artwork.PurchaseID (specifically the ID 'D4758', though I would like the program to be able to total all PurchaseID in the same query).

I then want the added together prices to be placed into Purchase.Total under the corresponding Purchase.PurchaseID row

I've tried multiple ways but I keep getting an SQL syntax error supposedly on Line 3, I've tried changing Artwork to Purchase but I still get it. I'm fairly new to Joins so I'm not sure what to do. Below is the code I developed:

UPDATE Purchase
SET Purchase.Total = SUM(Artwork.Price)
FROM Artwork
INNER JOIN Artwork ON Purchase.PurchaseID = Artwork.PurchaseID
WHERE Artwork.PurchaseID = 'D4758';

The result should change Purchase.Total from NULL to the decimal (5,2) SUM value of Artwork.Price that has a PurchaseID of 'D4758'.

I instead get the error

#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 Artwork INNER JOIN Artwork ON Purchase.PurchaseID = Artwork.PurchaseID WH' at line 3

Could you be more clear about the schema of your tables? Try providing some existing sample data in your table and how the data should be after it is changed, so that we could understand it better. — Maadesh Sivakumar, Jun 28 '19 at 09:34
You should review Multiple-table syntax: here https://dev.mysql.com/doc/refman/8.0/en/update.html the syntax you have used looks more like sqlserver — P.Salmon, Jun 28 '19 at 09:35
Possible duplicate of [MYSQL Update Statement Inner Join Tables](https://stackoverflow.com/questions/8057565/mysql-update-statement-inner-join-tables) — Raymond Nijland, Jun 28 '19 at 09:37
Possible duplicate of [MySQL update query with WHERE clause and INNER JOIN not working](https://stackoverflow.com/questions/26018065/mysql-update-query-with-where-clause-and-inner-join-not-working) — mayank, Jun 28 '19 at 09:38
It looks like you joined the table to itself instead of to purchases. — Sean Brookins, Jun 28 '19 at 13:48

score 1 · Answer 1 · edited Jun 28 '19 at 11:17

1

Solved:

UPDATE Purchase
    SET Total = (SELECT sum(Price)
                 FROM Artwork
                 WHERE Purchase.PurchaseID = Artwork.PurchaseID
                )
 WHERE Purchase.PurchaseID = 'D4758'

edited Jun 28 '19 at 11:17

Gordon Linoff

1,242,037
58
646
786

answered Jun 28 '19 at 09:47

huntron198

31
3

This is the only other answer that at least does what the OP want and has no syntax errors. – Gordon Linoff Jun 28 '19 at 11:18
sub-queries are heavy – mayank Jul 15 '19 at 16:30

mayank · Answer 2 · 2019-06-28T09:41:54.033

0

you need to write the set keyword after the join happens. Also remove the FROM keyword.

UPDATE Purchase as p
INNER JOIN Artwork as a ON p.PurchaseID = a.PurchaseID
SET p.Total = SUM(a.Price)
WHERE a.PurchaseID = 'D4758';

edited Jun 28 '19 at 09:41

answered Jun 28 '19 at 09:35

mayank

543
4
14

Thank you. This gives me this error though: #1066 - Not unique table/alias: 'Artwork' – huntron198 Jun 28 '19 at 09:39
@huntron198 try using the aliased, I have updated the answer. – mayank Jun 28 '19 at 09:42

score 0 · Answer 3 · answered Jun 28 '19 at 09:41

Your FROM clause has to point to the table you're trying to update (Purchase), and then JOIN with the related table (Artwork), so your query should be:

UPDATE
  Purchase
SET
  Purchase.Total = SUM(Artwork.Price)
FROM
  Purchase
INNER JOIN
  Artwork 
ON 
  Purchase.PurchaseID = Artwork.PurchaseID
WHERE
  Artwork.PurchaseID = 'D4758';

score 0 · Answer 4 · answered Jun 28 '19 at 09:42

Your query is based on update syntax for SQL Server where you write SET statement right after update. In mysql you should write the SET statement at the end.

UPDATE 
  Purchase pr
INNER JOIN 
  Artwork ar
ON 
  pr.PurchaseID = ar.PurchaseID
SET 
  pr.Total = SUM(ar.Price)
WHERE 
  ar.PurchaseID = 'D4758'

The syntax for set in mySQL is

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

score 0 · Answer 5 · answered Jun 28 '19 at 11:16

You can use UPDATE for this, but you want to calculate the SUM() in a subquery. I would recommend:

UPDATE Purchase p JOIN
       (SELECT aw.PurchaseID, SUM(aw.Price) as total_price
        FROM Artwork aw
        WHERE aw.PurchaseID = 'D4758'
        GROUP BY aw.PurchaseID
       ) aw
       ON p.PurchaseID = aw.PurchaseID
    SET p.Total = aw.total_price;

score 0 · Answer 6 · answered Jul 18 '20 at 04:35

0

Try this:

UPDATE Purchase as P
INNER JOIN Artwork as A ON P.PurchaseID = A.PurchaseID
SET P.Total = SUM(A.Price)
WHERE A.PurchaseID = 'D4758';

In update query, you don't use FROM keyword.

answered Jul 18 '20 at 04:35

Ankit Jindal

3,672
3
25
37

How to fix syntax error where FROM is incorrect when joining two tables?

6 Answers6