Skip to main content

SQL UPDATE Statement

Body

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

Submitted byMZEEWASHOO (not verified)on Sat, 07/07/2012 - 18:33

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.
Submitted byAnonymous (not verified)on Fri, 02/01/2013 - 13:59

  1. 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")
  2. con.Open()
  3. 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 [email protected], 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() & " "
  4.  
  5. cmd = New SqlCommand(_String, con)
  6. 'Dim update As New SqlCommand("Select * from Employee", con)
  7. '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 [email protected], 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)
  8. '" & TextBox1.Text & "'"
  9. 'update = New SqlCommand(str, con)
  10.  
  11. cmd.Parameters.Add(New SqlParameter("Firstname", SqlDbType.NVarChar, 50))
  12. cmd.Parameters("Firstname").Value = TextBox1.Text
  13. cmd.Parameters.Add(New SqlParameter("Lastname", SqlDbType.NVarChar, 50))
  14. cmd.Parameters("Lastname").Value = TextBox2.Text
  15. cmd.Parameters.Add(New SqlParameter("Fathername", SqlDbType.NVarChar, 50))
  16. cmd.Parameters("Fathername").Value = TextBox3.Text
  17. cmd.Parameters.Add(New SqlParameter("Nominename", SqlDbType.NVarChar, 50))
  18. cmd.Parameters("Nominename").Value = TextBox4.Text
  19. cmd.Parameters.Add(New SqlParameter("Gender", SqlDbType.NVarChar, 5))
  20. cmd.Parameters("Gender").Value = TextBox5.Text
  21. cmd.Parameters.Add(New SqlParameter("Address", SqlDbType.NVarChar, 50))
  22. cmd.Parameters("Address").Value = TextBox6.Text
  23. cmd.Parameters.Add(New SqlParameter("Pincode", SqlDbType.NChar, 10))
  24. cmd.Parameters("Pincode").Value = TextBox7.Text
  25. cmd.Parameters.Add(New SqlParameter("Contactnumber", SqlDbType.NChar, 15))
  26. cmd.Parameters("Contactnumber").Value = TextBox8.Text
  27. cmd.Parameters.Add(New SqlParameter("DOB", SqlDbType.Date))
  28. cmd.Parameters("DOB").Value = DateTimePicker1.Text
  29. cmd.Parameters.Add(New SqlParameter("City", SqlDbType.NChar, 10))
  30. cmd.Parameters("City").Value = TextBox9.Text
  31. cmd.Parameters.Add(New SqlParameter("Bankdetails", SqlDbType.NVarChar, 50))
  32. cmd.Parameters("Bankdetails").Value = TextBox10.Text
  33. cmd.Parameters.Add(New SqlParameter("Companyname", SqlDbType.NVarChar, 50))
  34. cmd.Parameters("Companyname").Value = TextBox11.Text
  35. cmd.Parameters.Add(New SqlParameter("Designation", SqlDbType.NVarChar, 50))
  36. cmd.Parameters("Designation").Value = TextBox12.Text
  37. cmd.Parameters.Add(New SqlParameter("ESINO", SqlDbType.NChar, 20))
  38. cmd.Parameters("ESINO").Value = TextBox13.Text
  39. cmd.Parameters.Add(New SqlParameter("PFNO", SqlDbType.NChar, 20))
  40. cmd.Parameters("PFNO").Value = TextBox14.Text
  41. cmd.Parameters.Add(New SqlParameter("Basicsalary", SqlDbType.NChar, 10))
  42. cmd.Parameters("Basicsalary").Value = TextBox15.Text
  43. cmd.Parameters.Add(New SqlParameter("DOJ", SqlDbType.Date))
  44. cmd.Parameters("DOJ").Value = DateTimePicker2.Text
  45. cmd.Parameters.Add(New SqlParameter("DOL", SqlDbType.Date))
  46. cmd.Parameters("DOL").Value = DateTimePicker3.Text
  47. cmd.Parameters.Add(New SqlParameter("HRA", SqlDbType.NChar, 10))
  48. cmd.Parameters("HRA").Value = TextBox16.Text
  49. cmd.Parameters.Add(New SqlParameter("BasicofPF", SqlDbType.NChar, 10))
  50. cmd.Parameters("BasicofPF").Value = TextBox17.Text
  51. cmd.Parameters.Add(New SqlParameter("Conv", SqlDbType.NChar, 10))
  52. cmd.Parameters("Conv").Value = TextBox18.Text
  53. cmd.Parameters.Add(New SqlParameter("Loanamount", SqlDbType.NVarChar, 20))
  54. cmd.Parameters("Loanamount").Value = TextBox19.Text
  55. cmd.Parameters.Add(New SqlParameter("Bonus", SqlDbType.NChar, 10))
  56. cmd.Parameters("Bonus").Value = Txtbonus.Text
  57. cmd.Parameters.Add(New SqlParameter("Otherded", SqlDbType.NChar, 10))
  58. cmd.Parameters("Otherded").Value = TextBox21.Text
  59. cmd.Parameters.Add(New SqlParameter("Imagedata", SqlDbType.Image))
  60. cmd.Parameters("Imagedata").Value = imgdata
  61. cmd.Parameters.Add(New SqlParameter("Imagename", SqlDbType.NChar, 10))
  62. cmd.Parameters("Imagename").Value = Txtfilename.Text
  63. cmd.Parameters.Add(New SqlParameter("Imagepath", SqlDbType.NVarChar, 200))
  64. cmd.Parameters("Imagepath").Value = Txtfilepath1.Text
  65. cmd.Parameters.Add(New SqlParameter("Grosssalary", SqlDbType.NChar, 10))
  66. cmd.Parameters("Grosssalary").Value = TxtGross.Text
  67. cmd.Parameters.Add(New SqlParameter("Remark", SqlDbType.NVarChar, 500))
  68. cmd.Parameters("Remark").Value = RTB1.Text
  69. cmd.ExecuteNonQuery()
  70. 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