Skip to main content

UPDATE in PostgreSQL

Using the UPDATE statement, we can change the value of the existing records in the table. Sometimes we need to change the value of the column(s) as per requirements. Suppose we have the "Employee" table and we need to update the salary of a particular employee, it means we will update the salary which already exists in the "Employee" table. But do it carefully because it is very critical to update any records into the table, you must apply where clause in update statement because if you don't use WHERE clause, then it will update all records of your table which may create a big issue for you. So please be careful before running the UPDATE statement.

PostgreSQL UPDATE syntax

UPDATE table_name
SET col1 = val1,
    col2 = val2 ,...

table_name is the name of the table which records you want to update, and it comes after UPDATE keywords.

col1,col1 is the column name of the table which particular value want to change. It comes after the SET keyword, and if you update values in multiple columns, you use a comma (,) to separate each pair of column and value.

WHERE clause is very crucial when you are updating any tables records.

PostgreSQL UPDATE examples

Suppose we have the following table. You can create an Employee table using the below PostgreSQL statement.

CREATE TABLE public."Employee"
    "Id" bigserial NOT NULL,
    "FName" text COLLATE pg_catalog."default",
    "LName" text COLLATE pg_catalog."default",
    "UserName" text COLLATE pg_catalog."default",
    "Password" text COLLATE pg_catalog."default",
    "Contact" text COLLATE pg_catalog."default",
    "Salary" numeric NOT NULL DEFAULT 0.0,

Insert some records on the above table("Employee")

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name1','last1', '', 'name@0123', '9088877787', '20000');

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name2','last2', '', 'name@0123', '9288877787', '30000');
INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary")
                VALUES ('Name3','last3', '', 'name@0123', '9388877787', '40000');

Illustrates of PostgreSQL UPDATE

Suppose the employee whose name is "Name2" got promotion and salary appraisal(50000), so the HR department needs to update employee salary in "Employee" table, so to update the particular employee's existing records we will use the following PostgreSQL statement.

UPDATE public."Employee"
                SET "Salary"=50000
                WHERE "UserName"='' AND "Password"='name@0123'

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])


{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str