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 ,...
WHERE
condition;
|
table_name is the name of
the table which records you want to update, and it comes after UPDATE keywords.
col1,col1 are the column
name of the table which particular value wants 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 table’s records, so be careful.
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,
CONSTRAINT
"PK_Test" PRIMARY
KEY ("Id")
)
|
Insert some records
on the above table ("Employee")
|
INSERT INTO public."Employee"(
"FName", "LName", "UserName", "Password", "Contact", "Salary")
VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000');
INSERT INTO public."Employee"(
"FName", "LName", "UserName", "Password", "Contact", "Salary")
VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000');
INSERT INTO public."Employee"(
"FName", "LName", "UserName", "Password", "Contact", "Salary")
VALUES ('Name3','last3', 'name3@gmail.com', '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"='name2@gmail.com' AND
"Password"='name@0123'
|
