UPDATE,DELETE Operation in PostgreSQL Database

As discussed in previous post you had learnt about two basic operations on database i.e. INSERT,SELECT ,in this post I am going to discuss the another two basic operations of PostgreSQL database i.e. UPDATE and DELETE.Lets get started.

UPDATE operation in PostgreSQL Database:

In this operation,we will make use of UPDATE keyword as used in simple SQL language just like below:

             String sql;
             sql = "UPDATE TABLE_NAME set column_name = value  where id = value;";
             st.executeUpdate(sql);

The example for this can be:

              String sql;
             sql = "UPDATE NEW_GARAGE set age = 25 where id = 4;";
             st.executeUpdate(sql); 

Complete code snippet for Update Operation:

package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Update {
    public static void main(String args[])
    {
    Connection c = null;
    Statement st = null;
     {
        try {
            Class.forName("org.postgresql.Driver");
             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kanwar","postgres","osm");
             c.setAutoCommit(false);
              st = c.createStatement();
             
             String sql;
             sql = "UPDATE NEW_GARAGE set age = 25 where id = 4;";
             st.executeUpdate(sql);
             c.commit();
         ResultSet rs = st.executeQuery( "SELECT * FROM NEW_GARAGE;" );
         while ( rs.next() )
         {
             int id = rs.getInt("id");
             String name = rs.getString("name");
              int age = rs.getInt("age");
      System.out.println( "ID = " + id );
      System.out.println( "NAME = " + name );
      System.out.println( "AGE = " + age );
      System.out.println();
         }
         rs.close();
         st.close();
         c.close();
        }
   
catch ( Exception e )
{
    System.err.println( e.getClass().getName()+": "+ e.getMessage() );
System.exit(0);
}
          System.out.println("Operation done successfully");  
 }
}
} 

The output for this program goes here:

DELETE Operation in  PostgreSQL Database:

Delete operation in PostgreSQL database is executed the same query as in SQL language.We use DELETE keyword and the data which we want to delete.The syntax for Delete operation is as follow:

String sql = "DELETE from Table_Name where column_name=value;";

The example of this syntax ca be:

String sql = "DELETE from NEW_GARAGE where ID=2;"; 

Complete code snippet for DELETE operation is as below:

package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Delete {
    public static void main(String args[])
    {
      Connection c =null;
      Statement st = null;
        try
        {
           Class.forName("org.postgresql.Driver");
           c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kanwar","postgres","osm");  
       
       c.setAutoCommit(false);
       System.out.println("Opened Database Successfully");
        st = c.createStatement();
        String sql = "DELETE from NEW_GARAGE where ID=2;";
        st.executeUpdate(sql);
        c.commit();
        ResultSet rs = st.executeQuery("SELECT * FROM NEW_GARAGE");
       
        while(rs.next())
        {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        int age = rs.getInt("age");
       
        System.out.println( "ID = " + id );  
          System.out.println( "NAME = " + name );
          System.out.println( "AGE = " + age );    
      System.out.println();
      }
      rs.close();
      st.close();
      c.close();
      }
        catch(Exception e)
        {
        System.err.println(e.getClass().getName()+":"+ e.getMessage());
        System.exit(0);
       
        }
        System.out.println("Operation performed Successfully");
        }
        }

     
The output for this program goes here:

Happy learning.Stay tuned.