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

No comments:

Post a Comment