[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

sql update statement with inner join

Hello,

I am using vb.net 2005 and an SQL Server database

i have 2 tables, I want to update a field in the first table if 2 fields in the second table are as specified.

I have tried the following code but it returns an error
"Incorrect syntax near the keyword INNER"

Any help would be greatly appreciated,

Thanks.
Dim comm As New SqlCommand("UPDATE Appointments INNER JOIN " & _
                                   "PatientDet ON Appointments.patientNIN = PatientDet.NIN " & _
                                   "SET Appointments.checkedIn = True " & _
                                   "WHERE PatientDet.NIN = '" & txtNin.Text.ToString.Trim & "'" & _
                                   "AND PatientDet.lastName = '" & txtSurname.Text.ToString.Trim & "'", dbHandling.getDbConn)
 
        Try
            comm.Connection.Open()
            comm.ExecuteNonQuery()
            comm.Connection.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
 
        End Try

Open in new window

0
pythonV
Asked:
pythonV
1 Solution
 
reb73Commented:
Try changing Line 1 as follows -
Dim comm As New SqlCommand("UPDATE Appointments SET Appointments.checkedIn = True " & _
                                   "FROM Appointments INNER JOIN " & _
                                   "PatientDet ON Appointments.patientNIN = PatientDet.NIN " & _
                                   "WHERE PatientDet.NIN = '" & txtNin.Text.ToString.Trim & "'" & _
                                   "AND PatientDet.lastName = '" & txtSurname.Text.ToString.Trim & "'", dbHandling.getDbConn)

Open in new window

0
 
pythonVAuthor Commented:
That did the trick!

Thanks alot,

Regards,

pythonV
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now