One place for hosting & domains

      Update

      Why You Should Update Your PHP Version (& How to Do It)


      Regularly updating your site’s copy of WordPress ensures that your website will have a high level of performance and security. If you forget to update your site’s PHP version though, you’re leaving your site vulnerable to both hackers and potential performance bottlenecks. Yikes!

      Fortunately, it’s fairly easy to check your site’s current PHP version and proceed with upgrades if it happens to be outdated. This process can contribute to faster load times, improved security, and support for new functionality.

      This article will discuss what PHP is, and why you should consider updating it. Then, we’ll show you how to check your current PHP version and upgrade it if needed. Let’s get started!

      An Introduction to PHP

      update your PHP version

      PHP is a programming and scripting language that plays an important role in WordPress development. PHP takes data from the database and converts it into an HTML web page whenever someone visits your website.

      As a website owner, you might never need to learn PHP coding. However, after downloading WordPress, you’ll automatically receive all of the PHP files that make up WordPress core:

      WordPress folder in FTP client

      Although these files have been written for you — and are just waiting for your content, themes, and plugins — there are some scenarios where you can benefit from editing PHP files:

      Since PHP is one of the key languages behind WordPress, learning a little bit about how to work with it will open up the scope of projects that you are able to do.

      It’s likely that you won’t have to edit your PHP files. Still, learning the language can be handy for developers who wish to create new software or customizations.

      Why You Should Update Your PHP Version

      You might already be familiar with the importance of updating your WordPress, themes, and plugins. It’s also crucial to regularly update your PHP version:

      PHP 8.1 released

      Currently, WordPress recommends upgrading your PHP version to at least 7.4. This isn’t required, but older versions may have reached their end of life, meaning they no longer have active support for bug fixes or security updates.

      Here are the main benefits of upgrading your sites to the latest PHP version:

      • Better security: Older PHP versions can be more vulnerable to cyberattacks.
      • Improved performance: Upgraded PHP can improve load times through internal code improvements and reduced memory usage.
      • More support: Currently supported versions have active support, with regular bug fixes and security patches.

      Take it from our Director of IT Operations, Luke Odom:

      “Running a modern, supported, version of PHP is one of the best things you can do for the health, security, and speed of your website. Let’s compare an outdated PHP version to an old car. Sure, it will run and get you from point A to B, but without modern safety sensors, technology, fuel efficiency, and GPS navigation you may find yourself in some trouble a bit more easily.”

      Ultimately, your website will be faster and more secure when it’s running on the latest version of PHP. Many web hosts provide automatic updates for their customers, but if you’re not with one of these hosts, you can follow this manual process.

      How to Check Your Current PHP Version

      Before you get started, you should check which PHP version is currently supporting your website. If you’ve installed WordPress 5.0 or higher, you can view your PHP version in your dashboard.

      First, go to Tools > Site Health:

      WordPress Tools Site Health

      On the Site Health page, click on Info. Then find the Server section:

      view server Site Health in WordPress

      Here, you should be able to see your current PHP version:

      view your server's PHP version

      Another simple method for checking your PHP version is using your web host’s control panel. For DreamHost websites, click on the Manage button next to your domain:

      manage websites in DreamPress Panel

      Then, find the section labeled PHP. This will list your site’s PHP version:

      modify your PHP version

      The latest PHP release is 8.1. If you have a version older than 7.4 — the version that the WordPress project suggests — you can proceed with the following steps.

      Skip the line and get tips right in your inbox

      Click below to sign up for more how-to’s and tutorials just like this one, delivered to your inbox.

      marketing tips

      How to Update Your PHP Version (In 4 Easy Steps)

      Once you decide to update your version of PHP, we recommend taking a few precautions. Firstly, consider backing up your website. If anything goes wrong, you can roll back your version of PHP and then revert your site to the backed-up version.

      Next, make sure your website is fully up to date. In your dashboard, go to the Updates page and install the latest versions of WordPress, themes, and plugins:

      WordPress Proxy Cache plugin

      After taking these preventative steps, you can start manually updating your PHP version. As a simpler alternative, you can also contact your hosting provider. The host can typically handle this process for you.

      Step 1: Create a Staging Site

      Updating your PHP version can cause software conflicts on your website. Therefore, it’s wise to test the update in a staging area initially. This creates a separate, safe environment to implement changes rather than breaking your live site.

      With many hosting providers, you can create a staging site through your control panel. For this tutorial, we’ll be using the DreamHost panel.

      If you have a managed DreamHost plan, you can set up your staging site in minutes. Simply open your control panel and navigate to WordPress > Managed WordPress > Manage:

      DreamPress managed WordPress settings in PanelOn the next page, go to Staging. Then, click on Create Staging Site:

      create a one click staging site with DreamPress managed WordPress hosting

      This will set up your staging site on an automatically generated subdomain. After this, you can update your PHP version without editing your live site.

      Step 2: Change Your PHP Version

      No matter your hosting provider, you can usually change your website’s PHP version through your control panel. This process will look different based on your host, but you’ll want to look for a MultiPHP Manager or PHP Selector.

      To edit your DreamHost staging site, you can simply scroll down to the Configure PHP section. This section will list your current PHP version and the most recent update option:

      change PHP version

      To update PHP, click on Change PHP to v8.1. If successful, this section will indicate that your site is up to date:

      configure your PHP version

      Although it isn’t recommended, you can also directly modify your live website. In your DreamHost panel, go to Manage Websites. Then, scroll down to PHP and click on the arrow next to Modify:

      select PHP version in the DreamHost panel

      Lastly, select the new version you want to run on your website. Click on Change PHP Version to finish the process.

      Step 3: Look for Conflicts

      After updating your PHP version, we recommend reviewing your plugins and themes for conflicts. Upgrading PHP may cause common problems such as the White Screen of Death or 500 internal server errors.

      Whether you updated a staging environment or your live site, visit the front end to see if everything is functioning correctly. If you notice any errors after browsing the website, you’ll need to troubleshoot the issue.

      One common reason for WordPress errors is a plugin conflict. When issues arise, try deactivating all of your plugins using the Bulk Actions on your Plugins page:

      deactivate WordPress plugins

      To target the conflicting plugin, reactivate each plugin one by one. Then, you can download a similar plugin or delete it if it’s unnecessary.

      If you face additional problems like exceeded maximum execution time or maintenance mode errors, you might need to edit your wp-config.php file. This process could require more extensive troubleshooting, such as increasing your PHP memory limit.

      Step 4: Publish Changes to Your Live Site

      If you implemented a new PHP version on a staging site, it’s time to publish this change on your live website. Usually, you can do this in your host’s control panel.

      For example, DreamHost provides a simple one-click transfer of your staging data to your website. Once you’re sure you’ve targeted any potential conflicts, you can click on Publish Staging to Live:

      push staging environment to live

      This will successfully update your website’s PHP version. Since you used a staging site, you won’t have to worry about unexpected errors!

      Ready for an Upgrade?

      You might be hesitant to update your PHP version because you’re worried about breaking your site. However, PHP updates ultimately improve the security and performance of your website. You can easily avoid any conflicts by testing changes before making them live.

      To review, here are the four steps you can take to update the PHP version of your website:

      1. Create a staging site.
      2. Change your PHP version.
      3. Look for conflicts.
      4. Publish changes to your live site.

      Although you can manually update your PHP version, you might want to avoid doing this for every new development. Here at DreamHost, our Website Maintenance plans now include automatic PHP updates! This way, you can sit back, relax, and leave this process to us.

      Website and PHP Version Management Made Easy

      Let us handle the backend — we’ll manage and monitor your website so it’s safe, secure, and always up.

      website tech support



      Source link

      How To Update Database Records in Laravel Eloquent



      Part of the Series:
      A Practical Introduction to Laravel Eloquent ORM

      Eloquent is an object relational mapper (ORM) that is included by default within the Laravel framework. In this project-based series, you’ll learn how to make database queries and how to work with relationships in Laravel Eloquent. To follow along with the examples demonstrated throughout the series, you’ll improve a demo application with new models and relationships. Visit the series introduction page for detailed instructions on how to download and set up the project.

      In a previous section of this series, you updated an existing Artisan command in order to support the new lists feature. Although there are commands to insert and delete links, the demo application currently doesn’t have a command to edit existing links. This can be useful to move links between lists, for instance, or update a link description.

      In this guide, you’ll create a new Artisan command to update existing links in the database.

      From your terminal, first make sure you’re in your project’s root directory, then run the following to bootstrap a new Artisan command:

      • docker-compose exec app php artisan make:command LinkUpdate

      This will create a new LinkUpdate.php file located at app/Console/Commands. Open the file in your code editor of choice:

      app/Console/Commands/LinkUpdate.php
      

      This file contains boilerplate code for a new Artisan command. You’ll update it to handle editing a link provided its unique id. This is what your handle() method needs to do:

      • Obtain an id provided by the user and check for the existence of a link with a matching id in the database.
      • If a valid link cannot be found, show an error message and exit.
      • If a valid link is found, prompt the user to provide updated values for the link description and link list.
      • Ask the user to confirm changes.
      • When confirmed, update the item in the database.

      Start by including a couple use definitions at the top of the file, to facilitate referencing to the Link and LinkList classes later on:

      app/Console/Commands/LinkUpdate.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      
      ...
      

      To obtain the link id, you should set up a mandatory argument in the new link:update command, so that users are required to provide that parameter at run time. Locate the command signature definition at the top of the file and replace it with the highlighted line:

      app/Console/Commands/LinkUpdate.php

      ...
      
      class LinkUpdate extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:update {link_id}";
      ...
      

      If you save the file and try to run the command now without an additional argument, you’ll get an error:

      • docker-compose exec app php artisan link:update

      Output

      Not enough arguments (missing: "link_id").

      In the handle() method, you need to obtain the link id provided by the user and locate it in the database. This can be done with the argument() method that is provided through the parent Command class. Then, you can use the find() Eloquent method to query the database for a link with that id. If the find() method returns null, it means no link with that id was found, so the program should exit in error.

      app/Console/Commands/LinkUpdate.php

      ...
         /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $link_id = $this->argument('link_id');
              $link = Link::find($link_id);
      
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              // obtain updated information from user
          }
      ...
      

      When a valid link is found, you need to prompt the user for the updated link information.You can do so using the ask method, highlighted in the next example:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      ...
      

      This code will prompt the user for an updated description and list, while keeping the current values as default in case a user doesn’t provide new ones, pressing ENTER to skip the prompt.

      Once you have all this information, you can proceed to the update. It’s a good idea to use the confirm() method to have the user confirm the changes before you run the database update. This is how such code would look:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      
              $this->info("Description: $link->description");
              $this->info("Listed in: " . $list_name);
      
              if ($this->confirm('Is this information correct?')) {
                  //code that updates the link
              }
      ...
      

      Inside the if block, you have to start by checking if the requested list exists, otherwise create a new list with the provided name. Then, you’ll use the associate() method to update the relationship between this link and its “parent” list. The save() method, finally, will persist the changes to the database:

      app/Console/Commands/LinkUpdate.php: function handle()

      ...
              if ($this->confirm('Is this information correct?')) {
                  $list = LinkList::firstWhere('slug', $list_name);
                  if (!$list) {
                      $list = new LinkList();
                      $list->title = $list_name;
                      $list->slug = $list_name;
                      $list->save();
                  }
                  $link->link_list()->associate($list)->save();
                  $this->info("Updated.");
              }
      ...
      

      This is the complete LinkUpdate.php file for your reference:

      app/Console/Commands/LinkUpdate.php

      <?php
      
      namespace AppConsoleCommands;
      
      use AppModelsLink;
      use AppModelsLinkList;
      use IlluminateConsoleCommand;
      
      class LinkUpdate extends Command
      {
          /**
           * The name and signature of the console command.
           *
           * @var string
           */
          protected $signature="link:update {link_id}";
      
          /**
           * The console command description.
           *
           * @var string
           */
          protected $description = 'Update a link in the database';
      
          /**
           * Create a new command instance.
           *
           * @return void
           */
          public function __construct()
          {
              parent::__construct();
          }
      
          /**
           * Execute the console command.
           *
           * @return int
           */
          public function handle()
          {
              $link_id = $this->argument('link_id');
              $link = Link::find($link_id);
      
              if ($link === null) {
                  $this->error("Invalid or non-existent link ID.");
                  return 1;
              }
      
              $link->description = $this->ask('Link Description (ENTER to keep current)') ?? $link->description;
              $list_name = $this->ask('Link List (ENTER to keep current)') ?? $link->link_list->title;
      
              $this->info("Description: $link->description");
              $this->info("Listed in: " . $list_name);
      
              if ($this->confirm('Is this information correct?')) {
                  $list = LinkList::firstWhere('slug', $list_name);
                  if (!$list) {
                      $list = new LinkList();
                      $list->title = $list_name;
                      $list->slug = $list_name;
                      $list->save();
                  }
                  $link->link_list()->associate($list)->save();
                  $this->info("Updated.");
              }
      
              return 0;
          }
      }
      

      Note: For more detailed information on Artisan commands, check our guide on How To Create Artisan Commands to Manage Database Records in Laravel, which is part of our introductory Laravel series.

      Save the file when you’re finished. Then, use the link:show command to obtain all links and its respective IDs:

      • docker-compose exec app php artisan link:show

      Output

      +----+-------------------------------------------------+--------------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+--------------+----------------------------------+ | 1 | https://digitalocean.com/community | default | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | default | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | default | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/digitalocean | social | Twitter | | 5 | https://dev.to/digitalocean | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+--------------+----------------------------------+

      Then, choose an item to edit. For instance, you may want to create a digitalocean list for the links that point to the DigitalOcean website (that would correspond to items with IDs 1, 2, and 3 in the previous example output).

      To update the link with ID 1, run:

      • docker-compose exec app php artisan link:update 1

      Output

      Link Description (ENTER to keep current): > DO Community Link List (ENTER to keep current): > digitalocean Description: DO Community Listed in: digitalocean Is this information correct? (yes/no) [no]: > y Updated.

      Then, run the link:show command again to see the updated information:

      Output

      +----+-------------------------------------------------+--------------+----------------------------------+ | id | url | list | description | +----+-------------------------------------------------+--------------+----------------------------------+ | 1 | https://digitalocean.com/community | digitalocean | DO Community | | 2 | https://digitalocean.com/community/tags/laravel | digitalocean | Laravel Tutorias at DigitalOcean | | 3 | https://digitalocean.com/community/tags/php | digitalocean | PHP Tutorials at DigitalOcean | | 4 | https://twitter.com/digitalocean | social | Twitter | | 5 | https://dev.to/digitalocean | social | DEV.to | | 6 | https://laravel.com/docs/8.x/eloquent | default | Laravel Eloquent Docs | +----+-------------------------------------------------+--------------+----------------------------------+

      In this guide, you learned how to update database records with Laravel Eloquent. You have upgraded the demo application to include a new command that allows users to edit existing links in the database.

      In the next and final part of this series, you’ll create a new command to delete a list of links.





      Source link

      How To Update Data in SQL


      Introduction

      When working with a database, there may be times when you need to change data that’s already been inserted into it. For example, you may need to correct a misspelled entry or perhaps you have new information to add to an incomplete record. Structured Query Language — more commonly known as SQL — provides the UPDATE keyword which allows users to change existing data in a table.

      This guide outlines how you can use SQL’s UPDATE syntax to change data in one or more tables. It also explains how SQL handles UPDATE operations that conflict with foreign key constraints.

      Prerequisites

      To follow this guide, you’ll need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, the exact syntax or output may differ if you test them on a system other than MySQL.

      • You’ll also need a database with some tables loaded with sample data which you can use to practice updating SQL data. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Connecting To MySQL and Setting Up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

      Create a database named updateDB:

      • CREATE DATABASE updateDB;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the updateDB database, run the following USE statement:

      Output

      Database changed

      After selecting the updateDB database, create a couple tables within it. For the examples used in this guide, imagine that you run a talent agency and have decided to begin tracking your clients and their performances in an SQL database. You plan to start off with two tables, the first of which will store information about your clients. You decide this table needs four columns:

      • clientID: each client’s identification number, expressed with the int data type. This column will also serve as the table’s primary key, with each value functioning as a unique identifier for its respective row
      • name: each client’s name, expressed using the varchar data type with a maximum of 20 characters
      • routine: a brief description of each client’s primary performance genre, again expressed using the varchar data type but with a maximum of 30 characters
      • performanceFee: a column to record each client’s standard performance fee, it uses the decimal data type with any values in this column limited to a maximum of five digits in length with two of those digits to the right of the decimal point. Thus, the range of values allowed in this column goes from -999.99 to 999.99

      Create a table named clients that has these four columns:

      • CREATE TABLE clients
      • (clientID int PRIMARY KEY,
      • name varchar(20),
      • routine varchar(30),
      • standardFee decimal (5,2)
      • );

      The second table will store information about your clients’ performances at a local venue. You decide this table needs five columns:

      • showID: similar to the clientID column, this column will hold a unique identification number for each show, expressed with the int data type. Likewise this column will serve as primary key for the shows table
      • showDate: the date of each performance. This column’s values are expressed using the date data type which uses the 'YYYY-MM-DD' format
      • clientID: the ID number of the client performing at the show, expressed as an integer
      • attendance: the number of attendees at each performance, expressed as an integer
      • ticketPrice: the price of an individual ticket at each show. This column uses the decimal data type with any values in this column limited to a maximum of four digits in length with two of those digits to the right of the decimal point, so the range of values allowed in this column is -99.99 to 99.99

      To ensure that the clientID column only holds values that represent valid client ID numbers, you decide to apply a foreign key constraint to the clientID column that references the clients table’s clientID column. A foreign key constraint is a way to express a relationship between two tables by requiring that values in the column on which it applies must already exist in the column that it references. In the following example, the FOREIGN KEY constraint requires that any value added to the clientID column in the shows table must already exist in the client table’s clientID column.

      Create a table named clients that has these five columns:

      • CREATE TABLE shows
      • (showID int PRIMARY KEY,
      • showDate date,
      • clientID int,
      • attendance int,
      • ticketPrice decimal (4,2),
      • CONSTRAINT client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients(clientID)
      • );

      Note that this example provides a name for the foreign key constraint: client_fk. MySQL will automatically generate a name for any constraint you add, but defining one here will be useful when we need to reference this constraint later on.

      Next, run the following INSERT INTO statement to load the clients table with five rows of sample data:

      • INSERT INTO clients
      • VALUES
      • (1, 'Gladys', 'song and dance', 180),
      • (2, 'Catherine', 'standup', 99.99),
      • (3, 'Georgeanna', 'standup', 45),
      • (4, 'Wanda', 'song and dance', 200),
      • (5, 'Ann', 'trained squirrel', 79.99);

      Then run another INSERT INTO statement to load the shows table with ten rows of sample data:

      • INSERT INTO shows
      • VALUES
      • (1, '2019-12-25', 4, 124, 15),
      • (2, '2020-01-11', 5, 84, 29.50),
      • (3, '2020-01-17', 3, 170, 12.99),
      • (4, '2020-01-31', 5, 234, 14.99),
      • (5, '2020-02-08', 1, 86, 25),
      • (6, '2020-02-14', 3, 102, 39.5),
      • (7, '2020-02-15', 2, 101, 26.50),
      • (8, '2020-02-27', 2, 186, 19.99),
      • (9, '2020-03-06', 4, 202, 30),
      • (10, '2020-03-07', 5, 250, 8.99);

      With that, you’re ready to follow the rest of the guide and begin learning about how to update data with SQL.

      Updating Data in a Single Table

      The general syntax of an UPDATE statement looks like this:

      • UPDATE table_name
      • SET column_name = value_expression
      • WHERE conditions_apply;

      Following the UPDATE keyword is the name of the table storing the data you want to update. After that is a SET clause which specifies which column’s data should be updated and how. Think of the SET clause as setting values in the specified column as equal to whatever value expression you provide.

      In SQL, a value expression — sometimes known as a scalar expression — is any expression that will return a single value for every row to be updated. This could be a string literal, or a mathematical operation performed on existing numeric values in the column. You must include at least one value assignment in every UPDATE statement, but you can include more than one to update data in multiple columns.

      After the SET clause is a WHERE clause. Including a WHERE clause in an UPDATE statement like in this example syntax allows you to filter out any rows that you don’t want to update. A WHERE clause is entirely optional in UPDATE statements, but if you don’t include one the operation will update every row in the table.

      To illustrate how SQL handles UPDATE operations, start by taking a look at all the data in the clients table. The following query includes an asterisk (*) which is SQL shorthand representing every column in the table, so this query will return all the data from every column in the clients table:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 180.00 | | 2 | Catherine | standup | 99.99 | | 3 | Georgeanna | standup | 45.00 | | 4 | Wanda | song and dance | 200.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      Say, for example, that you notice Katherine’s name is misspelled — it should begin with a “K” but in the table it begins with a “C” — so you decide to change that value by running the following UPDATE statement. This operation updates values in the name column by changing the name value of any row with the name Catherine to Katherine:

      • UPDATE clients
      • SET name="Katherine"
      • WHERE name="Catherine";

      Output

      Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

      This output indicates that only one row was updated. You can confirm this by running the previous SELECT query again:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 180.00 | | 2 | Katherine | standup | 99.99 | | 3 | Georgeanna | standup | 45.00 | | 4 | Wanda | song and dance | 200.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      This output shows that the value formerly reading Catherine has indeed been changed to Katherine.

      This example updated only one value in the name column. However, you can update multiple values with a less exclusive WHERE clause.

      To illustrate, imagine that you negotiate standard performance fees for all your clients who perform stand-up comedy or song and dance routines. The following statement will update values in the standardFee column by setting them equal to 140.

      Note that this example’s WHERE clause includes a LIKE operator so it only updates the performanceFee value for each client whose routine value matches the specified wildcard pattern, 's%'. In other words, it will update the performance fee of any performer whose routine starts with the letter “s”:

      • UPDATE clients
      • SET standardFee = 140
      • WHERE routine LIKE 's%';

      Output

      Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0

      If you query the contents of the clients table once again, the result set will confirm that four of your clients now have the same performance fee:

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 140.00 | | 2 | Katherine | standup | 140.00 | | 3 | Georgeanna | standup | 140.00 | | 4 | Wanda | song and dance | 140.00 | | 5 | Ann | trained squirrel | 79.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      If any columns in your table hold numeric values, you can update them using an arithmetic operation in the SET clause. To illustrate, say that you also negotiate a forty percent increase for each of your clients’ performance fees. To reflect this in the clients table, you could run an UPDATE operation like this:

      • UPDATE clients
      • SET standardFee = standardFee * 1.4;

      Output

      Query OK, 5 rows affected, 1 warning (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 1

      Note: Notice that this output indicates the update resulted in a warning. Oftentimes, MySQL will issue a warning when it’s forced to make a change to your data because of a column or table’s definition.

      MySQL provides the SHOW WARNINGS shortcut that can help explain any warnings you receive:

      Output

      +-------+------+--------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------+ | Note | 1265 | Data truncated for column 'standardFee' at row 5 | +-------+------+--------------------------------------------------+ 1 row in set (0.00 sec)

      This output informs us that the database system issued the warning because it had to truncate one of the new standardFee values so it would conform to the decimal format — five digits with two to the right of the decimal point — defined previously.

      Query the clients table once more to confirm that each of the clients’ performance fees have increased by forty percent.

      Output

      +----------+------------+------------------+-------------+ | clientID | name | routine | standardFee | +----------+------------+------------------+-------------+ | 1 | Gladys | song and dance | 196.00 | | 2 | Katherine | standup | 196.00 | | 3 | Georgeanna | standup | 196.00 | | 4 | Wanda | song and dance | 196.00 | | 5 | Ann | trained squirrel | 111.99 | +----------+------------+------------------+-------------+ 5 rows in set (0.00 sec)

      As mentioned previously, you can also update data in multiple columns with a single UPDATE statement. To do this, you must specify every column you want to update, following each with the respective value expression, and then separate each column and value expression pair with a comma.

      For example, say that you learn that the venue where your clients perform had misreported the number of attendees for all of Georgeanna and Wanda’s shows. By coincidence, you also happened to enter the wrong ticket price for each of their performances.

      Before updating the data in the shows table, run the following query to retrieve all the data currently held within it:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 124 | 15.00 | | 2 | 2020-01-11 | 5 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 170 | 12.99 | | 4 | 2020-01-31 | 5 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 25.00 | | 6 | 2020-02-14 | 3 | 102 | 39.50 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 202 | 30.00 | | 10 | 2020-03-07 | 5 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.01 sec)

      To reflect the actual numbers and prices, you update the table to add twenty attendees to each of their performances and increase each of their ticketPrice values by fifty percent. You could do so with an operation like the following:

      • UPDATE shows
      • SET attendance = attendance + 20,
      • ticketPrice = ticketPrice * 1.5
      • WHERE clientID IN
      • (SELECT clientID
      • FROM clients
      • WHERE name="Georgeanna" OR name="Wanda");

      Output

      Query OK, 4 rows affected, 1 warning (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 1

      Notice that this example uses a subquery in the WHERE clause to return Georgeanna and Wanda’s clientID values from the clients table. Oftentimes, abstract values like identification numbers can be hard to remember, but this method of using a subquery to find a value can be helpful when you only know certain attributes about the records in question.

      After updating the shows table, query it once again to confirm that the changes went through as expected:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 144 | 22.50 | | 2 | 2020-01-11 | 5 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 190 | 19.49 | | 4 | 2020-01-31 | 5 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 25.00 | | 6 | 2020-02-14 | 3 | 122 | 59.25 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 222 | 45.00 | | 10 | 2020-03-07 | 5 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

      Once again, this output indicates that the UPDATE statement completed successfully.

      Using JOIN Clauses to Update Data in Multiple Tables

      Up to this point, this guide has only shown how to update data in one table at a time. However, some SQL implementations allow you to update multiple columns in multiple tables by temporarily combining the tables with a JOIN clause.

      Here’s the general syntax you can use to update multiple tables with a JOIN clause:

      • UPDATE table_1 JOIN table_2
      • ON table_1.related_column = table_2.related_column
      • SET table_1.column_name = value_expression,
      • table_2.column_name = value_expression
      • WHERE conditions_apply;

      This example syntax begins with the UPDATE keyword followed by the names of two tables, separated by a JOIN clause. Following that is the ON clause, which describes how the query should join the two tables together.

      In most implementations, you can join tables by finding matches between any set of columns that have what the SQL standard refers to as “JOIN eligible” data types. This means that, in general, you can join any column holding numeric data with any other column that holds numeric data, regardless of their respective data types. Likewise, you can join any columns that hold character values with any other column holding character data.

      Note that because JOIN clauses compare the contents of more than one table, this example syntax specifies which table to select each column from by preceding the name of the column with the name of the table and a period. This is known as a fully qualified column reference. You can specify which table a column should be selected from like this for any operation, although it’s typically only used to improve clarity when working with multiple tables.

      To illustrate with the sample tables created previously, run the following UPDATE statement. This will join the clients and shows tables on their respective clientID columns, and then update the routine and ticketPrice values for Gladys’s record in the clients table and each of her performances listed in the shows table:

      • UPDATE clients JOIN shows
      • USING (clientID)
      • SET clients.routine="mime",
      • shows.ticketPrice = 30
      • WHERE name="Gladys";

      Output

      Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0

      Notice that this example joins the tables with the USING keyword instead of the ON keyword used in the previous example syntax. This is possible because both tables have a clientID column that share a similar data type.

      For a more in-depth tutorial on JOIN operations, see our guide on How To Use Joins in SQL.

      Changing Foreign Key UPDATE Behavior

      By default, any UPDATE statement that would cause a conflict with a FOREIGN KEY constraint will fail.

      Recall from the Connecting to MySQL and Setting up a Sample Database section of the Prerequisites that the clientID column of the shows table is a foreign key that references the clientID column of the clients table. This means that any value entered into the shows table’s clientID column must already exist in that of the clients table.

      If you attempt to update the clientID value of a record in the clients table that also appears in the clientID column of the shows table, it will cause an error:

      • UPDATE clients
      • SET clientID = 9
      • WHERE name="Ann";

      Output

      ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

      You can avoid this error by replacing the existing foreign key constraint with one that treats UPDATE operations differently.

      Note: Not every relational database management system or engine allows you to add or remove a constraint from an existing table as outlined in the following paragraphs. If you’re using an RDBMS other than MySQL, you should consult its official documentation to understand what limitations it has for managing constraints.

      To replace the current constraint, you must first remove it with an ALTER TABLE statement. Recall that in the CREATE TABLE statement for shows, we defined client_fk as the name for the table’s FOREIGN KEY constraint:

      • ALTER TABLE shows
      • DROP FOREIGN KEY client_fk;

      Output

      Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

      Following that, create a new foreign key constraint that’s configured to treat UPDATE operations in a way that makes sense for the given use case. Aside from the default setting which prohibits UPDATE statements that violate the foreign key, there are two other options available on most RDBMSs:

      • ON UPDATE SET NULL: This option will allow you to update records from the parent table, and will reset any values in the child table that reference them as NULL.
      • ON UPDATE CASCADE: When you update a row in the parent table, this option will cause SQL to automatically update any records that reference it in the child table so that they align with the new value in the parent table.

      For the purposes of this example, ON UPDATE SET NULL doesn’t make sense. After all, if you change one of your clients’ identification numbers but don’t remove them from the clients table, they should still be associated with their performances in the shows table. Their new identification number should be reflected in their performances’ records, so the ON UPDATE CASCADE option makes more sense for our purposes.

      To add a FOREIGN KEY constraint that follows the ON UPDATE CASCADE behavior, run the following ALTER TABLE statement. This creates a new constraint named new_client_fk which replicates the previous constraint definition, but includes the ON UPDATE CASCADE option:

      • ALTER TABLE shows
      • ADD CONSTRAINT new_client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients (clientID)
      • ON UPDATE CASCADE;

      Output

      Query OK, 10 rows affected (0.02 sec) Records: 10 Duplicates: 0 Warnings: 0

      This output tells you that the operation impacted all ten rows in the shows table.

      Note: Instead of altering a table’s definition to change how a foreign key handles UPDATE operations, you can define this behavior from the start in the CREATE TABLE statement like this:

      • CREATE TABLE shows
      • (showID int PRIMARY KEY,
      • showDate date,
      • clientID int,
      • attendance int,
      • ticketPrice decimal (4,2),
      • CONSTRAINT client_fk
      • FOREIGN KEY (clientID)
      • REFERENCES clients(clientID)
      • ON UPDATE CASCADE
      • );

      Following that, you’ll be able to update the clientID value of any record in the clients table, and those changes will cascade down to any rows in the shows table that reference it:

      • UPDATE clients
      • SET clientID = 9
      • WHERE name="Ann";

      Output

      Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

      Although this output says it only affected one row, it will have also updated the clientID value of any performance records in the shows table associated with Ann. To confirm this, run the following query to retrieve all the data from the shows table:

      Output

      +--------+------------+----------+------------+-------------+ | showID | showDate | clientID | attendance | ticketPrice | +--------+------------+----------+------------+-------------+ | 1 | 2019-12-25 | 4 | 144 | 22.50 | | 2 | 2020-01-11 | 9 | 84 | 29.50 | | 3 | 2020-01-17 | 3 | 190 | 19.49 | | 4 | 2020-01-31 | 9 | 234 | 14.99 | | 5 | 2020-02-08 | 1 | 86 | 30.00 | | 6 | 2020-02-14 | 3 | 122 | 59.25 | | 7 | 2020-02-15 | 2 | 101 | 26.50 | | 8 | 2020-02-27 | 2 | 186 | 19.99 | | 9 | 2020-03-06 | 4 | 222 | 45.00 | | 10 | 2020-03-07 | 9 | 250 | 8.99 | +--------+------------+----------+------------+-------------+ 10 rows in set (0.00 sec)

      As expected, the update made to the clientID column in the clients table cascaded down to the associated rows in the shows table.

      Conclusion

      By reading this guide, you learned how to change existing records in or more tables using SQL’s UPDATE statement. You also learned how SQL handles UPDATE operations that conflict with foreign key constraints, and how to change that default behavior.

      The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of how it handles UPDATE operations and what options are available for them.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link