Reading:  

Beginners guide to MySQL and MariaDB


Updating and Deleting data

Updating data in MySQL

UPDATE statement is used to modify any field value in MySQL table.

Syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

 

one or more field altogether can be updated. WHERE clause can be used to specify any condition.

Here is an example 

MySQL Update example

Here is how we can do the example same thing using a PHP sccript

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
      $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
      $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $sql = "UPDATE book_tbl set book_title='Learn C++ **' where book_id=2;";    
      $select->execute($sql);        
echo "Book title for Book ID 2 updated."; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } 

?>

Database user must have Update_priv to update data else an exception will be thrown.

For more details on UPDATE statement, please read MySQL docs here

Delete data in MySQL

DELETE command is used to modify any field value in MySQL table. For detailed documentation please read here 

Syntax:

DELETE FROM table_name [WHERE Clause]

If there is no WHERE clause specified, then all records will be deleted.

To delete selected record need to specify the condition in WHERE clause.

Here is an example screenshot showing DELETE statement in action

MySQL delete in action

 

From a PHP script here is how you can delete a row or multiple rows together.

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
      $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
      $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $sql = "DELETE FROM book_tbl where book_id=2;";    
      $select->execute($sql);        
echo "Book title for Book ID 2 deleted."; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } 

?>

In the next part of this tutorial, we will explore LIKE condition that is used in WHERE clause. 

Description

In this tutorial, we will cover few topics that will give you a heads on start to build your knowledge on. Topics that we will cover briefly but still providing enough information are listed below

  • Overview
  • Installing on Linux and Windows
  • Some useful admin queries for starters
  • Connection
  • Create Database
  • Drop Database
  • Select Database
  • Data Type
  • Create Table
  • Drop Table
  • Inserting and Selecting data
  • Where Clause
  • Updating and deleting data
  • Like Clause
  • Sorting Result
  • Using Joins
  • Brief introduction to Regex, Transactions and Indexes
  • Alter Command
  • Temporary Tables
  • Database Info
  • Using Sequence
  • Database Export and Import
  • Resetting MySQL/MariaDB Administrator password


Audience

Absolute beginners looking to get a sneak peak into what MySQL. Please remember that this is not a full on guide but a quick introduction to the subject.

Learning Objectives

Get to know MySQL and MariaDB

Author: Subject Coach
Added on: 23rd Jun 2015

You must be logged in as Student to ask a Question.

None just yet!