SQL syntax error. MySQL Update table SELECT

Question

I want to update a row based on the existence of an id in other table that is also in that table. In other words that id is a primary key in other table and just a column in the table I want to update.

I want to update based in the same id.

I have this query but it doesn't work because of the SQL syntax.

UPDATE
transaction
SET
    DaysRented = 3,
    Cost = 3,
    TotalCost= 5
FROM 
 transaction

INNER JOIN
    rentals
ON
     transaction.idRentals = rentals.idRentals;

Nagaraj S · Accepted Answer · 2014-05-12 04:24:09Z

2

syntax for mysql

http://www.mysqltutorial.org/mysql-update-join/

First, you specify the main table ( T1) and the table that you want the main table to join to ( T2) after the UPDATE clause. Notice that you must specify at least one table after the UPDATE clause. The data in the table that is not specified after the UPDATE clause is not updated.
Second, you specify a kind of join you want to use i.e., either INNER JOIN or LEFT JOIN and a join condition. Notice that the JOIN clause must appear right after the UPDATE clause.
Third, you assign new values to the columns in T1 and/or T2 tables that you want to update.

UPDATE transaction 
INNER JOIN rentals  ON transaction.idRentals = rentals.idRentals
SET DaysRented = 3,
    Cost = 3,
    TotalCost = 5;

answered May 12, 2014 at 4:24

Nagaraj S

13.5k6 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-05-12 04:17:47Z

2

You are using SQL Server update/join syntax. The proper MySQL syntax is:

UPDATE transaction INNER JOIN
       rentals
       ON transaction.idRentals = rentals.idRentals
SET DaysRented = 3,
    Cost = 3,
    TotalCost = 5;

answered May 12, 2014 at 4:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL syntax error. MySQL Update table SELECT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related