{"id":3096,"date":"2024-06-21T12:21:26","date_gmt":"2024-06-21T04:21:26","guid":{"rendered":"https:\/\/five.co\/?p=3096"},"modified":"2024-06-24T08:32:16","modified_gmt":"2024-06-24T00:32:16","slug":"mysql-update-with-join","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/mysql-update-with-join\/","title":{"rendered":"MySQL Update With Join: A Quick &#038; Easy Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Updating records in <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/what-is-mysql\/\">MySQL<\/a> often involves the use of joins to modify data across related tables. The MySQL UPDATE JOIN statement is a powerful tool that allows you to perform cross-table updates efficiently. In this article, we will explore how to use the <a href=\"https:\/\/www.mysql.com\/\" rel=\"nofollow noopener\" target=\"_blank\">MySQL<\/a> UPDATE JOIN statement and how Five can aid in this process.<\/p>\n\n\n<div class=\"root-eb-toc-0e5pk wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-0e5pk \"><div class=\"eb-toc-container eb-toc-0e5pk  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Introduction to MySQL UPDATE With JOIN&quot;,&quot;text&quot;:&quot;Introduction to MySQL UPDATE With JOIN&quot;,&quot;link&quot;:&quot;introduction-to-mysql-update-with-join&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Syntax of MySQL UPDATE With JOIN&quot;,&quot;text&quot;:&quot;Syntax of MySQL UPDATE With JOIN&quot;,&quot;link&quot;:&quot;syntax-of-mysql-update-with-join&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Integrating Five with MySQL UPDATE JOIN&quot;,&quot;text&quot;:&quot;Integrating Five with MySQL UPDATE JOIN&quot;,&quot;link&quot;:&quot;integrating-five-with-mysql-update-join&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Key Features of Five:&quot;,&quot;text&quot;:&quot;Key Features of Five:&quot;,&quot;link&quot;:&quot;key-features-of-five&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Working with SQL in Five&quot;,&quot;text&quot;:&quot;Working with SQL in Five&quot;,&quot;link&quot;:&quot;working-with-sql-in-five&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Example: Updating Employee Salaries&quot;,&quot;text&quot;:&quot;Example: Updating Employee Salaries&quot;,&quot;link&quot;:&quot;example-updating-employee-salaries&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Using LEFT JOIN&quot;,&quot;text&quot;:&quot;Using LEFT JOIN&quot;,&quot;link&quot;:&quot;using-left-join&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Introduction to MySQL UPDATE With JOIN&quot;,&quot;value&quot;:&quot;introduction-to-mysql-update-with-join&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Syntax of MySQL UPDATE With JOIN&quot;,&quot;value&quot;:&quot;syntax-of-mysql-update-with-join&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Integrating Five with MySQL UPDATE JOIN&quot;,&quot;value&quot;:&quot;integrating-five-with-mysql-update-join&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Key Features of Five:&quot;,&quot;value&quot;:&quot;key-features-of-five&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Working with SQL in Five&quot;,&quot;value&quot;:&quot;working-with-sql-in-five&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Example: Updating Employee Salaries&quot;,&quot;value&quot;:&quot;example-updating-employee-salaries&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Using LEFT JOIN&quot;,&quot;value&quot;:&quot;using-left-join&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#introduction-to-mysql-update-with-join\">Introduction to MySQL UPDATE With JOIN<\/a><li><a href=\"#syntax-of-mysql-update-with-join\">Syntax of MySQL UPDATE With JOIN<\/a><li><a href=\"#integrating-five-with-mysql-update-join\">Integrating Five with MySQL UPDATE JOIN<\/a><ul class=\"eb-toc__list\"><li><a href=\"#key-features-of-five\">Key Features of Five:<\/a><li><a href=\"#working-with-sql-in-five\">Working with SQL in Five<\/a><\/li><\/ul><li><a href=\"#example-updating-employee-salaries\">Example: Updating Employee Salaries<\/a><li><a href=\"#using-left-join\">Using LEFT JOIN<\/a><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL UPDATE With JOIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In MySQL, joins are commonly used to query rows from a table that may have matching rows in another table. The UPDATE JOIN statement extends this functionality by allowing you to update rows in one table based on values from another table. This is particularly useful for maintaining data consistency across related tables.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Syntax of MySQL UPDATE With JOIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The basic syntax for the MySQL UPDATE JOIN statement is as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"UPDATE T1\n[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2.C1\nSET T1.C2 = T2.C2, \n    T2.C3 = expr\nWHERE condition;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> T1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #E06C75\">[INNER JOIN | LEFT JOIN]<\/span><span style=\"color: #ABB2BF\"> T2 <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">T1<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">C1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">T2<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">C1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">T1<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">C2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">T2<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">C2<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">T2<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">C3<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> expr<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> condition;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s a breakdown of how it works:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>UPDATE T1<\/strong>: Specify the table you want to update.<\/li>\n\n\n\n<li><strong>JOIN T2 ON T1.C1 = T2.C1<\/strong>: Use either INNER JOIN or LEFT JOIN and a join predicate to define the relationship between the tables.<\/li>\n\n\n\n<li><strong>SET T1.C2 = T2.C2<\/strong>: Assign new values to the columns of the table you are updating.<\/li>\n\n\n\n<li><strong>WHERE condition<\/strong>: Specify conditions to filter the rows for updating.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Integrating Five with MySQL UPDATE JOIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Five is an excellent platform for rapidly building and deploying modern, data-driven business applications. With Five, you can easily manage your <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-a-front-end-for-a-mysql-database\/\">MySQL databases<\/a> and perform complex queries, including the UPDATE JOIN statements, through its SQL editor interface.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Key Features of Five:<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Build<\/strong>: Develop applications locally at no cost with Five\u2019s full-featured environment.<\/li>\n\n\n\n<li><strong>Test<\/strong>: Preview, inspect, and test applications before delivering them to end-users.<\/li>\n\n\n\n<li><strong>Deploy<\/strong>: Deploy applications to a secure and scalable cloud infrastructure.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Working with SQL in Five<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Five provides a seamless environment to work with MySQL databases. Whether you\u2019re creating tables, writing complex queries, or extending functionalities with custom code, Five has got you covered.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Create a MySQL Database Visually:<\/strong> With Five\u2019s visual database builder, you can create tables, define fields, set primary and foreign keys, and establish <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-many-to-many-relationships-in-sql\/\">relationships<\/a> with just a few clicks. This feature eliminates the need for manual SQL scripting and reduces the likelihood of errors.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-execute-a-query-in-sql-using-queries-and-data-views\/\">Query Data in SQL<\/a>:<\/strong> Five allows you to write SELECT, JOIN, and UPDATE statements directly or use its visual query builder. This flexibility ensures you can perform any database operation efficiently, whether you prefer coding or visual tools.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Custom Business Logic<\/strong>: Extend your applications beyond basic functionalities by writing <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/javascript-typescript-functions-inside-five\/\">JavaScript and TypeScript functions in Five. <\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Go From SQL Database to Web App<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Example: Updating Employee Salaries<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Consider a practical example where we have two tables: <code>employees<\/code> and <code>merits<\/code>. The <code>employees<\/code> table stores employee data, and the <code>merits<\/code> table stores performance ratings and corresponding merit percentages.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE DATABASE IF NOT EXISTS hr;\n\nUSE hr;\n\nCREATE TABLE merits (\n  performance INT PRIMARY KEY, \n  percentage DEC(11, 2) NOT NULL\n);\n\nCREATE TABLE employees (\n  emp_id INT AUTO_INCREMENT PRIMARY KEY, \n  emp_name VARCHAR(255) NOT NULL, \n  performance INT DEFAULT NULL, \n  salary DEC(11, 2) DEFAULT NULL, \n  FOREIGN KEY (performance) REFERENCES merits (performance)\n);\n\nINSERT INTO merits(performance, percentage) \nVALUES \n  (1, 0), \n  (2, 0.01), \n  (3, 0.03), \n  (4, 0.05), \n  (5, 0.08);\n\nINSERT INTO employees(emp_name, performance, salary) \nVALUES \n  ('Mary Doe', 1, 50000), \n  ('Cindy Smith', 3, 65000), \n  ('Sue Greenspan', 4, 75000), \n  ('Grace Dell', 5, 125000), \n  ('Nancy Johnson', 3, 85000), \n  ('John Doe', 2, 45000), \n  ('Lily Bush', 3, 55000);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">IF<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NOT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">EXISTS<\/span><span style=\"color: #ABB2BF\"> hr;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">USE<\/span><span style=\"color: #ABB2BF\"> hr;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">merits<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  performance <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">percentage<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DEC<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">11<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">employees<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  emp_id <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> AUTO_INCREMENT <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  emp_name <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  performance <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DEFAULT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  salary <\/span><span style=\"color: #C678DD\">DEC<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">11<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">DEFAULT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #C678DD\">FOREIGN KEY<\/span><span style=\"color: #ABB2BF\"> (performance) <\/span><span style=\"color: #C678DD\">REFERENCES<\/span><span style=\"color: #ABB2BF\"> merits (performance)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> merits(performance, <\/span><span style=\"color: #C678DD\">percentage<\/span><span style=\"color: #ABB2BF\">) <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">01<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">03<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">05<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">08<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> employees(emp_name, performance, salary) <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Mary Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">50000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Cindy Smith&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">65000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Sue Greenspan&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">75000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Grace Dell&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">125000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Nancy Johnson&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">85000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;John Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">45000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Lily Bush&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">3<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">55000<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose you want to increase each employee\u2019s salary by their merit percentage. You can use the following UPDATE JOIN statement:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"UPDATE employees \nINNER JOIN merits ON employees.performance = merits.performance \nSET salary = salary + salary * percentage;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> employees <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> merits <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">employees<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">performance<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">merits<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">performance<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> salary <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> salary + salary * <\/span><span style=\"color: #C678DD\">percentage<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query updates the <code>salary<\/code> column in the <code>employees<\/code> table by performing an INNER JOIN with the <code>merits<\/code> table based on matching values in the <code>performance<\/code> column.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Using LEFT JOIN<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In some cases, you might need to update rows even if there are no matching rows in the joined table. This is where the LEFT JOIN comes in handy. For instance, if we have new hires whose performance data is not yet available:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"INSERT INTO employees(emp_name, performance, salary) \nVALUES \n  ('Jack William', NULL, 43000), \n  ('Ricky Bond', NULL, 52000);\n\nUPDATE employees \nLEFT JOIN merits ON employees.performance = merits.performance \nSET salary = salary + salary * COALESCE(percentage, 0.015);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> employees(emp_name, performance, salary) <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Jack William&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">43000<\/span><span style=\"color: #ABB2BF\">), <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">  (<\/span><span style=\"color: #98C379\">&#39;Ricky Bond&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">52000<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> employees <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> merits <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">employees<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">performance<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">merits<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">performance<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> salary <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> salary + salary * <\/span><span style=\"color: #56B6C2\">COALESCE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">percentage<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">015<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query ensures that even employees without performance ratings receive a salary increment.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Using the MySQL UPDATE JOIN statement allows you to efficiently update data across related tables, ensuring data consistency and integrity. Five makes this process even more straightforward by providing a robust platform for building, testing, and deploying your applications. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To get started with Five,&nbsp;<a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\">sign up for a free trial<\/a>&nbsp;and follow one of our code-along tutorials.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Updating records in MySQL often involves the use of joins to modify data across related tables. The MySQL UPDATE JOIN statement is a powerful tool that allows you to perform cross-table updates efficiently. In this article, we will explore how to use the MySQL UPDATE JOIN statement and how Five can aid in this process. [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":3098,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-3096","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/06\/Five.Co-MySQL-Update-With-Join-A-Quick-Easy-Guide.png",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Updating records in MySQL often involves the use of joins to modify data across related tables. The MySQL UPDATE JOIN statement is a powerful tool that allows you to perform cross-table updates efficiently. In this article, we will explore how to use the MySQL UPDATE JOIN statement and how Five can aid in this process.&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=3096"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3096\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/3098"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=3096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=3096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=3096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}