Getting error of incorrect syntax near 'INNER'

[dndaughtery]

I'm getting an error ("syntax error near 'INNER'") with the following sql but I can't find where the problem is. Can some help out with this?

UPDATE CollectionAgencyProgram_T_EC INNER JOIN (Rejects INNER JOIN Card ON Rejects.CardID = Card.CardID) ON CollectionAgencyProgram_T_EC.ProgramID = Card.ProgramID SET Rejects.CollectionAgencyID = CollectionAgencyProgram_T_EC.CollectionAgencyID
WHERE CollectionAgencyProgram_T_EC.GovernmentID Is Null

 

[gmmastros]

UPDATE Rejects
SET Rejects.CollectionAgencyID = CollectionAgencyProgram_T_EC.CollectionAgencyID
From CollectionAgencyProgram_T_EC
INNER JOIN (Rejects
INNER JOIN Card ON Rejects.CardID = Card.CardID)
ON CollectionAgencyProgram_T_EC.ProgramID = Card.ProgramID
WHERE CollectionAgencyProgram_T_EC.GovernmentID Is Null

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dndaughtery]

Thanks, I have trouble reading queries built with Access Wizards. Could you help me with this one? It's my last one.

UPDATE CollectionAgencyProgram_T_EC INNER JOIN (Rejects INNER JOIN Card ON Rejects.CardID = Card.CardID) ON CollectionAgencyProgram_T_EC.ProgramID = Card.ProgramID SET Rejects.CollectionAgencyID = CollectionAgencyProgram_T_EC.CollectionAgencyID
WHERE CollectionAgencyProgram_T_EC.GovernmentID Is Null

 

[gmmastros]

Access update queries and SQL Server update queries are abviously different.

With Access:

Update Table1 
       Inner Join Table2 On table1.Field = Table2.Field
Set    Table1.OtherField = Table2.OtherField
Where  SomeCondition = Whatever

With SQL Server

Update Table1
Set    Table1.OtherField = Table2.OtherField
From   Table1
       Inner Join Table2 On Table1.Field = Table2.Field
Where  SomeCondition = Whatever

For the first update query, I just copy/pasted and moved some things around a little. Why don't you give it a shot. If you can't make it work, then post back here and we'll help some more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Are you working with a SQL Server database or an Access database? If it's an Access database, you should be posting in an Access forum on this site. This forum is for SQL Server and as George stated, the syntax can be different.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

SQLBill,

From other posts, I know dndaughtery is in the process of converting from Access to SQL Server. He's having a rough go of it, but is steadily making progress.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

That's good to know and thanks for pointing that out. I just didn't want the poster to get stuck with scripts that don't work if they were really discussing Access.

-SQLBill

Posting advice: FAQ481-4875