SQL UPDATE Statement

The UPDATE statement is used to update records in a table.

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

SQL UPDATE Example

The "Student" table:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Colombo	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST105	L.Lavanya	Galle	2	Female	1989-04-22	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1
ST110	K.Mala	NULL	1	NULL	NULL	NULL

Now we want to update the Student_no='ST103'; in the "student" table.

We use the following SQL statement:

UPDATE  Student 
SET Address='Kalutura' 
WHERE Student_no='ST103';


The "student" table will now look like this:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Kalutura	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST105	L.Lavanya	Galle	2	Female	1989-04-22	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1
ST110	K.Mala	NULL	1	NULL	NULL	NULL

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

UPDATE  Student 

SET Address='Kalutura';

The "student" table will now look like this:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kalutura	1	Male	1988-01-17	C1
ST101	N.Kumanan	Kalutura	2	Male	1989-12-15	C2
ST102	S.Ranjini	Kalutura	1	Female	1989-01-17	C1
ST103	K.Dias	Kalutura	3	Male	1987-07-22	C1
ST104	A.Kavitha	Kalutura	3	Female	1985-03-20	C2
ST105	L.Lavanya	Kalutura	2	Female	1989-04-22	C2
ST106	G.Moorthy	Kalutura	2	Male	1986-01-12	C1
ST107	F.Nathan	Kalutura	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Kalutura	1	Male	1990-12-12	C3
ST109	K.Ravi	Kalutura	1	Male	1988-02-17	C1
ST110	K.Mala	Kalutura	1	NULL	NULL	NULL