Question Why I have error on UPDATE SQL command?

[NewComer]

I have in same SQL database with

. a table RoomList with 5 columns: RoomNum, Daily, Weekend, Holiday, Special
with 1 row has RoomNum values of 102

. a table AssignedList with 6 columns: RNum, RDaily, RWeekend, RHoliday, Special, GConf
with 1 row has ordered values are 102 $50.00 $60.00 $70.00 $100.00 '12345'

*) Except the GConf equal String, the other fields are same types

- I want to copy those 4 rates from table AssignedList where GConf = '12345' to table RoomList from same Room 102

==================================================================================

strSQL = "UPDATE RoomList SET "
strSQL = strSQL & "Daily = RDaily, Weekend = RWeekend, Holiday = RHoliday "
strSQL = strSQL & "FROM RoomList INNER JOIN AssignedList ON RoomNum = RNum "
strSQL = strSQL & "WHERE GConf = '12345'"
cmdMSA = New OleDbCommand(strSQL, dbConn2)

Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If

Catch ex As Exception
MessageBox.Show("Error Update RoomList from AssignedList = " & ex.Message)
End Try

cmdMSA.Dispose()

==================================================================================

Show ---> Error Update RoomList from AssignedList = Syntax error (missing operator) in query
expression 'RHoliday FROM RoomList INNER JOIN AssignedList ON RoomNum = RNum'

Note: I follow this link Update one table with another tables values - MS SQL

*) Can someone help otherwise?

 

[InertiaM]

Go back and look at the link you supplied. In there, you will find this:-

UPDATE TBL1SET TBL1.COL = TBL2.COL2FROM TBL1 INNER JOIN TBL2ON TBL1.ID=TBL2.IDWHERE TBL2.COL >=1

As you can see, you need to qualify your field names with the corresponding table names.

 

[NewComer]

I did modify codes but I still have error

Thank for pointing it out, I did modify codes but I still have error: Syntax error (missing operator) in query expression 'AssignedList.RSpecial FROM RoomList INNER JOIN AssignedList ON RoomList.RoomNum = AssignedList.RNum'

Here is my new codes:

'Update RoomList from AssignedList for Rates

strSQL = "UPDATE RoomList SET "

strSQL = strSQL & "RoomList.Daily = AssignedList.RDaily, RoomList.Weekend = AssignedList.RWeekend, "

strSQL = strSQL & "RoomList.Holiday = AssignedList.RHoliday, RoomList.Special = AssignedList.RSpecial "

strSQL = strSQL & "FROM RoomList INNER JOIN AssignedList ON RoomList.RoomNum = AssignedList.RNum "

strSQL = strSQL & "WHERE AssignedList.GConf = '" & WebNumber.ToString & "'"

cmdMSA = New OleDbCommand(strSQL, dbConn2)

Try
If Not (cmdMSA.ExecuteScalar() = Nothing) Then
f_Failed = True
End If
Catch ex As Exception
MessageBox.Show("Error Update RoomList from AssignedList = " & ex.Message)

End Try

cmdMSA.Dispose()

Any suggestions?

 

[InertiaM]

strSQL = ""
strSQL &= " UPDATE"
strSQL &= "   RoomList"
strSQL &= " SET"
strSQL &= "   RoomList.Daily = AssignedList.RDaily,"
strSQL &= "   RoomList.Weekend = AssignedList.RWeekend,"
strSQL &= "   RoomList.Holiday = AssignedList.RHoliday,"
strSQL &= "   RoomList.Special = AssignedList.RSpecial"
strSQL &= " FROM"
strSQL &= "   RoomList, AssignedList"
strSQL &= " WHERE"
strSQL &= "   RoomList.RoomNum = AssignedList.RNum"
strSQL &= " AND"
strSQL &= "   AssignedList.GConf = @WEBNUMBER"

See the link in my signature relating to parameterised queries.

 

[NewComer]

My fault to forget mentioning that it is MS Access

I did try your codes but it still having error, however I forgot to mention that those 2 tables are in MS Access database

- I forgot to mention it, because I am able to use with standard UPDATE command & I also use another SQL database at the same time

I have another help from MS Access & it works here are the update codes:

strSQL = "UPDATE RoomList INNER JOIN AssignedList ON RoomList.RoomNum = AssignedList.RNum "

strSQL = strSQL & "SET RoomList.Daily = [AssignedList].[RDaily], RoomList.Weekend = [AssignedList].[RWeekend], "

strSQL = strSQL & "RoomList.Holiday = [AssignedList].[RHoliday], RoomList.Special = [AssignedList].[RSpecial] "

Thanks for trying help