MySQL Syntax for Join Update

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 the UPDATE clause.
  • Table aliases makes the query easier to write and to read.
  • I am guessing that enabled and active 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



Leave a reply



Submit