PostgreSQL UPDATE

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL UPDATE

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.

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.

Structure of the table

Command to update data

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.

Update PostgreSQL data with PHP

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

  1. <!DOCTYPE html>  
  2. <head>  
  3. <title>UPDATE PostgreSQL data with PHP</title>  
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />  
  5. <style>li {list-style: none;}</style>  
  6. </head>  
  7. <body>  
  8. <h2>Supply bookid and enter</h2>  
  9. <ul>  
  10. <form name="display" action="enter-bookid.php" method="POST" >  
  11. <li>Book ID:</li>  
  12. <li><input type="text" name="bookid" /></li>  
  13. <li><input type="submit" name="submit" /></li>  
  14. </form>  
  15. </ul>  
  16. <php$db = pg_connect("host=localhost port=5432 dbname=postgres user=postgres password=admin123");  
  17. $result = pg_query($db, "SELECT * FROM book where book_id = '$_POST[bookid]'");  
  18. $row = pg_fetch_assoc($result);  
  19. if (isset($_POST['submit'])){  
  20. echo "<ul><form name='update' action='enter-bookid.php' method='POST' >  
  21. <li>Book ID:</li>  
  22. <li><input type='text' name='bookid_updated' value='$row[book_id]' /></li>  
  23. <li>Book Name:</li>  
  24. <li><input type='text' name='book_name_updated' value='$row[name]' /></li>  
  25. <li>Price (USD):</li><li><input type='text' name='price_updated' value='$row[price]' /></li>  
  26. <li>Date of publication:</li>  
  27. <li><input type='text' name='dop_updated' value='$row[date_of_publication]' /></li>  
  28. <li><input type='submit' name='new' /></li>  
  29. </form>  
  30. </ul>";}  
  31. if (isset($_POST['new'])){  
  32. $result = pg_query($db, "UPDATE book SET book_id = $_POST[bookid_updated],  
  33. name = '$_POST[book_name_updated]',price = $_POST[price_updated],  
  34. date_of_publication = $_POST[dop_updated]");  
  35. if (!$result){  
  36. echo "Update failed!!";  
  37. }  
  38. else  
  39. {  
  40. echo "Update successfull;";  
  41. }   
  42. }?>  
  43. </body>  
  44. </html>