Mysql update with join example

Also, the query is also filtered by the WHERE clause against certain specified conditions so that the rows of the table can be updated properly. We can also set multiple column values to update multiple table columns which can be parted by comma ,. For demonstration, let us create a database for a sample to use in the query which includes the tables to be applied for the MySQL Update Join. Suppose, we have created two tables in the database:. The other table Merit is created having columns for student performances and percentage of merit.

Now, under this database, we will design two tables as mentioned above Students and Merit which will be used for further Update Join query. Using INNER join let us update the Salary column values in Students table based on the student performance and percentage calculation from Merit table using the succeeding query:. Take our short survey.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Update with self-join Ask Question. Asked 11 years, 10 months ago.

Active 4 years ago. Viewed 34k times. Improve this question. MattH MattH 3, 2 2 gold badges 27 27 silver badges 32 32 bronze badges. Simplified update query using JOIN -ing multiple tables. SET ft. Vinit Kadkol Vinit Kadkol 1, 11 11 silver badges 11 11 bronze badges. Another example why SQL isn't really portable. For MySQL it would be: update ud, sale set ud. Yada Yada For Sybase I found the update needs to be on the table itself not the alias: update ud set u.

Ken Goh Ken Goh 1, 11 11 silver badges 8 8 bronze badges. DivisionId from divisions join users on divisions. Sheryar Nizar Sheryar Nizar 4 4 silver badges 4 4 bronze badges.

Example Scenario You have a table of users. This table has rows You then have a table of users to disable at one go because you find out they've all done something bad. Bonus It's more readable. All the conditions are in one place and the rows to update are in one place. Luke Watts Luke Watts 4 4 silver badges 8 8 bronze badges. Richard Richard 2 2 gold badges 10 10 silver badges 21 21 bronze badges.

Try this one, I think this will works for you update ud set ud. Mustapha Larhrouch 3, 2 2 gold badges 12 12 silver badges 28 28 bronze badges. FelixSFD 5, 10 10 gold badges 41 41 silver badges bronze badges. C1 SET T1. C2, T2. David Oliver 2, 23 23 silver badges 33 33 bronze badges. Thank you, DavidOliver, for fixing my answer. I am asking because you can also do something like the following: update B set B.

John Kane John Kane 4, 1 1 gold badge 25 25 silver badges 40 40 bronze badges. That examples updates the first table in the join order. I know its been LONG, however, you could pick a column in any table. If you just have to update one table.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog.