I am trying to convert a query from Access 2007 into SQL 2005. The original access query looks like this: UPDATE (Collector_Move_Table INNER JOIN dbo_UVW_SR_ACCOUNT ON Collector_Move_Table.AccountID = dbo_UVW_SR_ACCOUNT.AccountID) INNER JOIN dbo_Bunch ON Collector_Move_Table.Move_To = dbo_Bunch.Description
SET Collector_Move_Table.[Exception] = False, dbo_UVW_SR_ACCOUNT.QueueID = [dbo_Bunch]![BunchID]
WHERE (((Collector_Move_Table.Queue) Like "Dia*" Or (Collector_Move_Table.Queue) Like "Ski*"));
I am getting a syntax error for the '(' on the first line but don't know of any other way to convey the double inner join for the statement. Does anyone know how this should be written to run in SQL?
Looks like noone else is going to, so I'll take a stab... I'm not completely sure if this will work, as I don't really have a sample set of data to work with... Nevertheless, here goes. The first script is a straight conversion of your Access query, which results in an error in SQLServer2005, as you described. The second one successfully completes the syntax check. I would highly recommend backing up your data prior to executing, in case it did not work as expected. --Straight Conversion of your original code. UPDATE DatabaseName.dbo.Collector_Move_Table CMT INNER JOIN DatabaseName.dbo.UVW_SR_ACCOUNT USA ON CMT.AccountID = USA.AccountID INNER JOIN DatabaseName.dbo.Bunch B ON CMT.Move_To = B.Description SET CMT.Exception = 'False', USA.QueueID = B.BunchID WHERE CMT.Queue Like 'Dia*' Or CMT.Queue Like 'Ski*' --Modified version, using joins within the where statement. UPDATE DatabaseName.dbo.Collector_Move_Table SET CMT.Exception = 'False', USA.QueueID = (SELECT B.BunchID FROM DatabaseName.dbo.Collector_Move_Table CMT INNER JOIN DatabaseName.dbo.UVW_SR_ACCOUNT USA ON CMT.AccountID = USA.AccountID INNER JOIN DatabaseName.dbo.Bunch B ON CMT.Move_To = B.Description WHERE CMT.Queue Like 'Dia*' Or CMT.Queue Like 'Ski*') WHERE CMT.Queue Like 'Dia*' Or CMT.Queue Like 'Ski*'Good luck!
Select * From Users Where Clue > 0;
0 rows affected