MySQL syntax for Join Update
MySQL supports a multi-table UPDATE
syntax, which would look approximately like this:
UPDATE Reservations r JOIN Train t ON (r.Train = t.TrainID)
SET t.Capacity = t.Capacity + r.NoSeats
WHERE r.ReservationID = ?;
You can update the Train
table and delete from the Reservations
table in the same transaction. As long as you do the update first and then do the delete second, it should work.
How can I do three table JOINs in an UPDATE query?
The answer is yes, you can.
Try it like this:
UPDATE TABLE_A a
JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b
JOIN TABLE_C c ON [condition]
SET a.column_c = a.column_c + 1
For a general update join:
UPDATE TABLEA a
JOIN TABLEB b ON a.join_colA = b.join_colB
SET a.columnToUpdate = [something]
MySQL Update Inner Join tables query
Try this:
UPDATE business AS b
INNER JOIN business_geocode AS g ON b.business_id = g.business_id
SET b.mapx = g.latitude,
b.mapy = g.longitude
WHERE (b.mapx = '' or b.mapx = 0) and
g.latitude > 0
Update:
Since you said the query yielded a syntax error, I created some tables that I could test it against and confirmed that there is no syntax error in my query:
mysql> create table business (business_id int unsigned primary key auto_increment, mapx varchar(255), mapy varchar(255)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table business_geocode (business_geocode_id int unsigned primary key auto_increment, business_id int unsigned not null, latitude varchar(255) not null, longitude varchar(255) not null, foreign key (business_id) references business(business_id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> UPDATE business AS b
-> INNER JOIN business_geocode AS g ON b.business_id = g.business_id
-> SET b.mapx = g.latitude,
-> b.mapy = g.longitude
-> WHERE (b.mapx = '' or b.mapx = 0) and
-> g.latitude > 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
See? No syntax error. I tested against MySQL 5.5.8.
MYSQL - UPDATE/INNER JOIN
The following should to the trick for you.
UPDATE favorites
INNER JOIN images ON favorites.image_id= images.id
SET favorites.archive = 1
WHERE favorites.user_id = '1';
MySQL Update statement with INNER JOIN
The syntax is wrong. Maybe you should try something like this:
UPDATE gradings g
INNER JOIN subjects ss ON ss.subject_id = g.subject_id
INNER JOIN students s ON s.id = g.student_id
INNER JOIN school_years sy ON sy.id = g.sy_id
INNER JOIN teachers t ON t.id = g.teacher_id
set g.first_q = 90
WHERE s.id = 1;
MySQL Update Inner Join tables query
Update query with join not working MySQL
The correct syntax in MySQL is:
UPDATE rates_master rm JOIN
rates_master_old rmo
ON rm.id = rmo.id;
SET rm.fees_information = rmo.fees_information ;
sql update with inner join and where
Error 1064 is a MySQL syntax error. The correct MySQL syntax is:
UPDATE newsreactions nr INNER JOIN
users u
ON nr.memberId = u.id
SET nr.enabled = 0
WHERE u.active = 0 AND u.comment LIKE '%spam%';
Notes:
- The
JOIN
goes in theUPDATE
clause. - Table aliases makes the query easier to write and to read.
- I am guessing that
enabled
andactive
are really numeric values. If so, do not use single quotes.
MySQL update a joined table
The multi-table UPDATE syntax in MySQL is different from Microsoft SQL Server. You don't need to say which table(s) you're updating, that's implicit in your SET clause.
UPDATE tableA a
JOIN tableB b
ON a.a_id = b.a_id
JOIN tableC c
ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
AND c.val > 10;
There is no FROM clause in MySQL's syntax.
UPDATE with JOIN is not standard SQL, and both MySQL and Microsoft SQL Server have implemented their own ideas as an extension to standard syntax.
MySQL syntax for update with join
UPDATE `Group` JOIN (SELECT GroupId, SUM(User.points) as sumuser FROM User
JOIN UsersGroups
ON User.id = UsersGroups.UserId
GROUP BY UsersGroups.GroupId) x
ON `Group`.Id=x.GroupId
SET group_score =x.sumuser
Syntax for UPDATE with LEFT JOIN
Parallel to this question I suggest that you use WHERE ... IN SELECT
. Also, you need to pass the ID of each row from the subselect to the main query, and since you didn't provide a table schema, we can only guess here:
UPDATE Test_table2
SET Pledge_Status = closed
WHERE [your id column] IN (
SELECT [your id column]
FROM Test_table2 AS recipients
LEFT JOIN Test_table2 AS pledgers
ON recipients.GIFT_NO = pledgers.PLEDGE_GIFT_NO
GROUP BY recipients.GIFT_NO
HAVING recipients.Pledge_Amt >= SUM(pledgers.Pledge_payment_amt)
/*ORDER BY recipients.CRSID ASC <-- no need to order anything here */
);
Related Topics
Converting Epoch Timestamp to SQL Server(Human Readable Format)
Using the Result of an Expression (E.G. Function Call) in a Stored Procedure Parameter List
Get Record Counts For All Tables in MySQL Database
Best Way to Check For "Empty or Null Value"
Illegal Mix of Collations MySQL Error
Difference Between Two Dates in MySQL
How to Order by With Union in Sql
Using Union and Order by Clause in MySQL
What Are Covering Indexes and Covered Queries in SQL Server
Spark SQL Window Function With Complex Condition
Why Does MySQL Allow "Group By" Queries Without Aggregate Functions
Query With Left Join Not Returning Rows For Count of 0
How to Convert an Integer (Time) to Hh:Mm:Ss::00 in SQL Server 2008
Postgresql Parameterized Order By/Limit in Table Function
Query Error with Ambiguous Column Name in SQL