PostgreSQL UPDATE

Last update on November 13 2023 09:57:03 (UTC/GMT +8 hours)

UPDATE Command

This document discusses how to update data of a table using PostgreSQL UPDATE command. We have also covered how to do the same using PHP-PostgreSQL.

UPDATE command is used to modify existing data of a table.

Usage

Following is the usage of PostgreSQL UPDATE command to modify data of a PostgreSQL table.

Code:

UPDATE table_name SET column_name1 = new_value1, column_name2 = new_value2, column_name3 = new_value3 WHERE some_column_name = existing_value;

Where table_name is the associated table, column1, 2, 3 are column names and new_value 1, 2, 3 are values to be modified to, some_column_name is a column name of the associated table and existing_value is the value present in the some_column_name column.

This is not the only format to update a table, but in all cases, you have to use SET keyword and supply the new value to an existing value of a column or a number of columns.

Update data example

Structure of the table

Following is the structure of the table whose data will be updated.

Data Before update

data before update

Command to update data

Code:

UPDATE book SET price = 19.49 WHERE price = 25.00

The following command will set a new price 19.49 if the price of any of the books in the table is 25.00.

Data after update

data after update

Update PostgreSQL data with PHP

Following PHP script (say enter-bookid.php) will update the existing data in our book table.

Code:

<!DOCTYPE html>
<head>
<title>UPDATE PostgreSQL data with PHP</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style>li {list-style: none;}</style>
</head>
<body>
<h2>Supply bookid and enter</h2>
<ul>
<form name="display" action="enter-bookid.php" method="POST" >
<li>Book ID:</li>
<li><input type="text" name="bookid" /></li>
<li><input type="submit" name="submit" /></li>
</form>
</ul>
<php$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=admin123");
$result = pg_query($db, "SELECT * FROM book where book_id = '$_POST[bookid]'");
$row = pg_fetch_assoc($result);
if (isset($_POST['submit'])){
echo "<ul><form name='update' action='enter-bookid.php' method='POST' >
<li>Book ID:</li>
<li><input type='text' name='bookid_updated' value='$row[book_id]' /></li>
<li>Book Name:</li>
<li><input type='text' name='book_name_updated' value='$row[name]' /></li>
<li>Price (USD):</li><li><input type='text' name='price_updated' value='$row[price]' /></li>
<li>Date of publication:</li>
<li><input type='text' name='dop_updated' value='$row[date_of_publication]' /></li>
<li><input type='submit' name='new' /></li>
</form>
</ul>";}
if (isset($_POST['new'])){
$result = pg_query($db, "UPDATE book SET book_id = $_POST[bookid_updated],
name = '$_POST[book_name_updated]',price = $_POST[price_updated],
date_of_publication = $_POST[dop_updated]");
if (!$result){
echo "Update failed!!";
}
else
{
echo "Update successfull;";
} 
}?>
</body>
</html>

Previous: Insert Data
Next: Delete Data