SQL MULTIPLE TABLE UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL MULTIPLE TABLE UPDATE

Author

Topic

tang3li2
Starting Member

2 Posts

Posted - 2010-08-17 : 01:56:33

hi, i'm having trouble in updating my table in sql server 2008, first i implemented a project using ms access 2007 the update is fine, somehow when i converted the table to sql server 2008, my update is not working it says, error near '(' here's my code..

Multiple update..

sqlSTR = "UPDATE ((TBL_Deffective_PO_Details INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID) " & _
"INNER JOIN TBL_Stocks_Balances ON TBL_Purchase_Detail.Item_ID = TBL_Stocks_Balances.Item_ID) " & _
"SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") - (sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text) & ", " _
& "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & ", " _
& "TBL_Purchase_Detail.Total_Price=" & total_price & ", " _
& "TBL_Stocks_Balances.Item_QTY =" & "TBL_Stocks_Balances.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & _
" WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
" AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text

it works on access without error, but in sql in has an error saying Incorrent syntax near '('

why is that happening? it works on ms access but in sql server it doesnt'????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-17 : 02:14:43

It is really hard for us to read concatenated code like that. Could you instead print out sqlSTR for us so that we can verify the syntax of the SQL query? It is much easier if you wrapped this into a stored procedure instead, at least from my perspective.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-17 : 02:29:35

I guess you dont need two braces after UPDATE

See what happens when you try this

Also it is better to post at ACCESS forum

sqlSTR = "UPDATE (TBL_Deffective_PO_Details INNER JOIN TBL_Purchase_Detail ON TBL_Deffective_PO_Details.Purchase_Detail_ID = TBL_Purchase_Detail.Purchase_Detail_ID) " & _
"INNER JOIN TBL_Stocks_Balances ON TBL_Purchase_Detail.Item_ID = TBL_Stocks_Balances.Item_ID " & _
"SET def_QTY =" & sqlDT.Rows(0)("Def_QTY") - (sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text) & ", " _
& "TBL_Purchase_Detail.Item_QTY =" & "TBL_Purchase_Detail.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & ", " _
& "TBL_Purchase_Detail.Total_Price=" & total_price & ", " _
& "TBL_Stocks_Balances.Item_QTY =" & "TBL_Stocks_Balances.Item_QTY + " & sqlDT.Rows(0)("Def_QTY") - lstitems.Items(I).SubItems(6).Text & _
" WHERE TBL_Deffective_PO_Details.Purchase_ID =" & txtpo.Text & _
" AND TBL_Deffective_PO_Details.Purchase_Detail_ID =" & lstitems.Items(I).SubItems(1).Text

Madhivanan

Failing to plan is Planning to fail

tang3li2
Starting Member

2 Posts

Posted - 2010-08-17 : 02:33:22

yep i already done that, and it still say incorrecty syntax near '('

Sachin.Nand

2937 Posts

Posted - 2010-08-17 : 02:33:26

SQL supports update the underlying way

UPDATE TBL_Deffective_PO_Details SET def_QTY=.....

than

UPDATE TBL_Deffective_PO_Details INNER JOIN TBL_Purchase_Detail as in the case of Access.

So maybe you can tweet your original query the way I mentioned above.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources