SQL UPDATE Statement

Submitted by admin on Monday, February 28, 2011 - 21:23.

When you insert record to your table there are some cases that you need to modify the data. Or you may want to update it to the latest record.

The SQL update is there to help us modify the existing values of the table, the SQL update statement used the same column name as insert statement and the new value that you want to placed and the table name to update the data successfully.

The syntax of SQL update statement is as follows:

UPDATE table_name SET column1=value1, column2=value2,... WHERE column=value

Consider the same table we had in previous tutorials.

Users:

Firstname	Lastname	Age	Maritalstatus	Country
John	Smith	40	Married	Usa
Mathew	Simon	30	Married	UK
Bill	Steve	20	Single	Usa
Amanda	Rogers	28	Married	Germany
Steve	Hills	30	Single	france
Tom	Jerry	20	Single	USA
Bill	Josh	30	Married	UK

Let’s say that john smith has migrated from USA to Australia then we want to update his country, then we write the SQL statement as:

UPDATE Users SET Country=”Australia” WHERE (Firstname=”John” AND Lastname=”Smith”)

The table will be updated and will change to:

Firstname	Lastname	Age	Maritalstatus	Country
John	Smith	40	Married	Australia
Mathew	Simon	30	Married	UK
Bill	Steve	20	Single	Usa
Amanda	Rogers	28	Married	Germany
Steve	Hills	30	Single	france
Tom	Jerry	20	Single	USA
Bill	Josh	30	Married	UK

Now consider multiple update, we want to change the age and marital status of Bill in the table.

NOTE: We have 2 persons named as Bill both will be updated.

UPDATE Users SET age=”50”, Maritalstatus=”Married” WHERE Firstname=”Bill”

The table is modified as follows:

Firstname	Lastname	Age	Maritalstatus	Country
John	Smith	40	Married	Australia
Mathew	Simon	30	Married	UK
Bill	Steve	50	Married	Usa
Amanda	Rogers	28	Married	Germany
Steve	Hills	30	Single	france
Tom	Jerry	20	Single	USA
Bill	Josh	50	Married	UK

So it is always a good idea to select rows with primary keys, or unique IDs to avoid unwanted data changes.

If we accidently or somehow forgot to type the where clause in the update query then all the data will be updated with the value you provide.

For example:

UPDATE Users SET Age=”10”

The result would be:

Firstname	Lastname	Age	Maritalstatus	Country
John	Smith	10	Married	Australia
Mathew	Simon	10	Married	UK
Bill	Steve	10	Married	Usa
Amanda	Rogers	10	Married	Germany
Steve	Hills	10	Single	france
Tom	Jerry	10	Single	USA
Bill	Josh	10	Married	UK

So make sure that you always use the where clause to avoid unnecessary lose of data.

Comments

update

the code above works when you are quering the database. but i want a code that i can use in vb 2010 that can work. when any record is being edited it updates the record on that same row. please help me with that.

UPDATE Problems

 Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
                con.Open()
                Dim _String As String = "UPDATE Employee SET Firstname = @Firstname, Lastname = @Lastname, Fathername = @Fathername, Nominename = @Nominename, Gender = @Gender, Address = @Address, Pincode = @Pincode, Contactnumber = @Contactnumber, DOB = @DOB,  City = @City, Bankdetails = @Bankdetails,  Companyname = @Companyname, Designation = @Designation, ESINO = @ESINO, PFNO = @PFNO, Basicsalary = @Basicsalary, DOJ =@DOJ, DOL = @DOL, HRA = @HRA,  BasicofPF = @BasicofPF,  Conv = @Conv, Loanamount = @Loanamount,  Bonus = @Bonus, Otherded = @Otherded, Imagedata = @Imagedata, Imagename = @Imagename, Grosssalary = @Grosssalary, Remark = @Remark where EmployerID = " & DGV2.CurrentRow.Cells(0).Value() & " "
 
                cmd = New SqlCommand(_String, con)
                'Dim update As New SqlCommand("Select * from Employee", con)
                'Dim update As New SqlCommand("UPDATE Employee SET Firstname = @Firstname, Lastname = @Lastname, Fathername = @Fathername, Nominename = @Nominename, Gender = @Gender, Address = @Address, Pincode = @Pincode, Contactnumber = @Contactnumber, DOB = @DOB,  City = @City, Bankdetails = @Bankdetails,  Companyname = @Companyname, Designation = @Designation, ESINO = @ESINO, PFNO = @PFNO, Basicsalary = @Basicsalary, DOJ =@DOJ, DOL = @DOL, HRA = @HRA,  BasicofPF = @BasicofPF,  Conv = @Conv, Loanamount = @Loanamount,  Bonus = @Bonus, Otherded = @Otherded, Imagedata = @Imagedata, Imagename = @Imagename, Grosssalary = @Grosssalary, Remark = @Remark where Firstname = " & DGV2.CurrentRow.Cells(1).Value() & "", con)
                '" & TextBox1.Text & "'"
                'update = New SqlCommand(str, con)
 
                cmd.Parameters.Add(New SqlParameter("Firstname", SqlDbType.NVarChar, 50))
                cmd.Parameters("Firstname").Value = TextBox1.Text
                cmd.Parameters.Add(New SqlParameter("Lastname", SqlDbType.NVarChar, 50))
                cmd.Parameters("Lastname").Value = TextBox2.Text
                cmd.Parameters.Add(New SqlParameter("Fathername", SqlDbType.NVarChar, 50))
                cmd.Parameters("Fathername").Value = TextBox3.Text
                cmd.Parameters.Add(New SqlParameter("Nominename", SqlDbType.NVarChar, 50))
                cmd.Parameters("Nominename").Value = TextBox4.Text
                cmd.Parameters.Add(New SqlParameter("Gender", SqlDbType.NVarChar, 5))
                cmd.Parameters("Gender").Value = TextBox5.Text
                cmd.Parameters.Add(New SqlParameter("Address", SqlDbType.NVarChar, 50))
                cmd.Parameters("Address").Value = TextBox6.Text
                cmd.Parameters.Add(New SqlParameter("Pincode", SqlDbType.NChar, 10))
                cmd.Parameters("Pincode").Value = TextBox7.Text
                cmd.Parameters.Add(New SqlParameter("Contactnumber", SqlDbType.NChar, 15))
                cmd.Parameters("Contactnumber").Value = TextBox8.Text
                cmd.Parameters.Add(New SqlParameter("DOB", SqlDbType.Date))
                cmd.Parameters("DOB").Value = DateTimePicker1.Text
                cmd.Parameters.Add(New SqlParameter("City", SqlDbType.NChar, 10))
                cmd.Parameters("City").Value = TextBox9.Text
                cmd.Parameters.Add(New SqlParameter("Bankdetails", SqlDbType.NVarChar, 50))
                cmd.Parameters("Bankdetails").Value = TextBox10.Text
                cmd.Parameters.Add(New SqlParameter("Companyname", SqlDbType.NVarChar, 50))
                cmd.Parameters("Companyname").Value = TextBox11.Text
                cmd.Parameters.Add(New SqlParameter("Designation", SqlDbType.NVarChar, 50))
                cmd.Parameters("Designation").Value = TextBox12.Text
                cmd.Parameters.Add(New SqlParameter("ESINO", SqlDbType.NChar, 20))
                cmd.Parameters("ESINO").Value = TextBox13.Text
                cmd.Parameters.Add(New SqlParameter("PFNO", SqlDbType.NChar, 20))
                cmd.Parameters("PFNO").Value = TextBox14.Text
                cmd.Parameters.Add(New SqlParameter("Basicsalary", SqlDbType.NChar, 10))
                cmd.Parameters("Basicsalary").Value = TextBox15.Text
                cmd.Parameters.Add(New SqlParameter("DOJ", SqlDbType.Date))
                cmd.Parameters("DOJ").Value = DateTimePicker2.Text
                cmd.Parameters.Add(New SqlParameter("DOL", SqlDbType.Date))
                cmd.Parameters("DOL").Value = DateTimePicker3.Text
                cmd.Parameters.Add(New SqlParameter("HRA", SqlDbType.NChar, 10))
                cmd.Parameters("HRA").Value = TextBox16.Text
                cmd.Parameters.Add(New SqlParameter("BasicofPF", SqlDbType.NChar, 10))
                cmd.Parameters("BasicofPF").Value = TextBox17.Text
                cmd.Parameters.Add(New SqlParameter("Conv", SqlDbType.NChar, 10))
                cmd.Parameters("Conv").Value = TextBox18.Text
                cmd.Parameters.Add(New SqlParameter("Loanamount", SqlDbType.NVarChar, 20))
                cmd.Parameters("Loanamount").Value = TextBox19.Text
                cmd.Parameters.Add(New SqlParameter("Bonus", SqlDbType.NChar, 10))
                cmd.Parameters("Bonus").Value = Txtbonus.Text
                cmd.Parameters.Add(New SqlParameter("Otherded", SqlDbType.NChar, 10))
                cmd.Parameters("Otherded").Value = TextBox21.Text
                cmd.Parameters.Add(New SqlParameter("Imagedata", SqlDbType.Image))
                cmd.Parameters("Imagedata").Value = imgdata
                cmd.Parameters.Add(New SqlParameter("Imagename", SqlDbType.NChar, 10))
                cmd.Parameters("Imagename").Value = Txtfilename.Text
                cmd.Parameters.Add(New SqlParameter("Imagepath", SqlDbType.NVarChar, 200))
                cmd.Parameters("Imagepath").Value = Txtfilepath1.Text
                cmd.Parameters.Add(New SqlParameter("Grosssalary", SqlDbType.NChar, 10))
                cmd.Parameters("Grosssalary").Value = TxtGross.Text
                cmd.Parameters.Add(New SqlParameter("Remark", SqlDbType.NVarChar, 500))
                cmd.Parameters("Remark").Value = RTB1.Text
                cmd.ExecuteNonQuery()
                MsgBox("Data + Image Updated Into DataBase..", MsgBoxStyle.Information)

Now the problem is that the data is get updated in the first row only ,no matter which row I selected,the first row gets updated ,How do I change in where clause so that the row I select from DataGridView gets updated only . thanks in Advance Email:[email protected]

Add new comment

Add new comment
202 views

SQL UPDATE Statement

Tags

Comments

update

UPDATE Problems

Add new comment