|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Trouble using Table data typehello, I'm using Table data type as a suggestion to lower Stack use. I'm just
having trouble figuring out how to use the table var correctly. 1 update @MDMB 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT 3 from @MDMBTotaisAssoc BIT 4 where BIT.MRCSKA = '-' and 5 @MDMB.MRCUIC = BIT.MRCUIC I get an error in line 2: Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 Line 2: Incorrect syntax near '.'. Thanks in advance Try like this
1 update @MDMB 2 set MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT 3 from @MDMBTotaisAssoc BIT 4 where BIT.MRCSKA = '-' and 5 @MDMB.MRCUIC = BIT.MRCUIC OR 1 update @MDMB 2 set MDMBMT = MDMBMT - BIT.MDMBMT 3 from @MDMBTotaisAssoc BIT 4 where BIT.MRCSKA = '-' and 5 @MDMB.MRCUIC = BIT.MRCUIC Regards Sivakumar Show quote "João Costa" wrote: > hello, I'm using Table data type as a suggestion to lower Stack use. I'm just > having trouble figuring out how to use the table var correctly. > > 1 update @MDMB > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > I get an error in line 2: > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > Line 2: Incorrect syntax near '.'. > > Thanks in advance > > > Tryed both ways, it didn't work I got the message:
Must declare the variable '@MDMB'. in the first case for line 2 in the second case for line 5 tryied a third case 1 update @MDMB 2 set MDMBMT = MDMBMT - BIT.MDMBMT 3 from @MDMBTotaisAssoc BIT 4 where BIT.MRCSKA = '-' and 5 MRCUIC = BIT.MRCUIC I got 2 messages: Ambiguous column name 'MDMBMT'. Ambiguous column name 'MRCUIC'. Show quote "Subramaniam Sivakumar" wrote: > Try like this > > 1 update @MDMB > 2 set MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > OR > > 1 update @MDMB > 2 set MDMBMT = MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > > Regards > Sivakumar > > > "João Costa" wrote: > > > hello, I'm using Table data type as a suggestion to lower Stack use. I'm just > > having trouble figuring out how to use the table var correctly. > > > > 1 update @MDMB > > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > > 3 from @MDMBTotaisAssoc BIT > > 4 where BIT.MRCSKA = '-' and > > 5 @MDMB.MRCUIC = BIT.MRCUIC > > > > I get an error in line 2: > > > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > > Line 2: Incorrect syntax near '.'. > > > > Thanks in advance > > > > > > The alias defined in the FROM clause must be used in the entire UPDATE
statement. ML Did that and got the message:
Cannot use the column prefix 'X'. This must match the object in the UPDATE clause '@MDMB'. and if I use the prefix @MDMB I get an error also Any Ideas anyone? Show quote "ML" wrote: > The alias defined in the FROM clause must be used in the entire UPDATE > statement. > > > ML Yes but I also tried the x on my own, sorry for not mentioning
Show quote "ML" wrote: > Subramaniam used 'BIT' as the alias, not 'X'. > > > ML See if this helps:
UPDATE @MDMB SET MDMBMT = MDMBMT - [BIT].MDMBMT from @MDMB AS X INNER JOIN @MDMBTotaisAssoc AS [BIT] ON [BIT].MRCSKA = '-' AND X.MRCUIC = [BIT].MRCUIC You should use an alias for column names when using table variables. "João Costa" <JooCo***@discussions.microsoft.com> wrote in message hello, I'm using Table data type as a suggestion to lower Stack use. I'mnews:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... just having trouble figuring out how to use the table var correctly. 1 update @MDMB 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT 3 from @MDMBTotaisAssoc BIT 4 where BIT.MRCSKA = '-' and 5 @MDMB.MRCUIC = BIT.MRCUIC I get an error in line 2: Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 Line 2: Incorrect syntax near '.'. Thanks in advance Didn't work
Show quote "Narayana Vyas Kondreddi" wrote: > See if this helps: > > UPDATE @MDMB > SET MDMBMT = MDMBMT - [BIT].MDMBMT > from @MDMB AS X > INNER JOIN > @MDMBTotaisAssoc AS [BIT] > ON [BIT].MRCSKA = '-' > AND X.MRCUIC = [BIT].MRCUIC > > You should use an alias for column names when using table variables. > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "João Costa" <JooCo***@discussions.microsoft.com> wrote in message > news:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... > hello, I'm using Table data type as a suggestion to lower Stack use. I'm > just > having trouble figuring out how to use the table var correctly. > > 1 update @MDMB > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > I get an error in line 2: > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > Line 2: Incorrect syntax near '.'. > > Thanks in advance > > > > > Try this:
UPDATE @MDMB SET MDMBMT = X.MDMBMT - [BIT].MDMBMT from @MDMB AS X INNER JOIN @MDMBTotaisAssoc AS [BIT] ON [BIT].MRCSKA = '-' AND X.MRCUIC = [BIT].MRCUIC "João Costa" <JooCo***@discussions.microsoft.com> wrote in message Didn't worknews:D64F080D-1F34-4EFB-BBF4-FC9E1E13B362@microsoft.com... Show quote "Narayana Vyas Kondreddi" wrote: > See if this helps: > > UPDATE @MDMB > SET MDMBMT = MDMBMT - [BIT].MDMBMT > from @MDMB AS X > INNER JOIN > @MDMBTotaisAssoc AS [BIT] > ON [BIT].MRCSKA = '-' > AND X.MRCUIC = [BIT].MRCUIC > > You should use an alias for column names when using table variables. > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "João Costa" <JooCo***@discussions.microsoft.com> wrote in message > news:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... > hello, I'm using Table data type as a suggestion to lower Stack use. I'm > just > having trouble figuring out how to use the table var correctly. > > 1 update @MDMB > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > I get an error in line 2: > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > Line 2: Incorrect syntax near '.'. > > Thanks in advance > > > > > It does compile, it seems that the problem is passed. I will debug and I'll
let you know Thanks Narayana! Show quote "Narayana Vyas Kondreddi" wrote: > Try this: > UPDATE @MDMB > SET MDMBMT = X.MDMBMT - [BIT].MDMBMT > from @MDMB AS X > INNER JOIN > @MDMBTotaisAssoc AS [BIT] > ON [BIT].MRCSKA = '-' > AND X.MRCUIC = [BIT].MRCUIC > > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "João Costa" <JooCo***@discussions.microsoft.com> wrote in message > news:D64F080D-1F34-4EFB-BBF4-FC9E1E13B362@microsoft.com... > Didn't work > > "Narayana Vyas Kondreddi" wrote: > > > See if this helps: > > > > UPDATE @MDMB > > SET MDMBMT = MDMBMT - [BIT].MDMBMT > > from @MDMB AS X > > INNER JOIN > > @MDMBTotaisAssoc AS [BIT] > > ON [BIT].MRCSKA = '-' > > AND X.MRCUIC = [BIT].MRCUIC > > > > You should use an alias for column names when using table variables. > > -- > > HTH, > > Vyas, MVP (SQL Server) > > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > > > > "João Costa" <JooCo***@discussions.microsoft.com> wrote in message > > news:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... > > hello, I'm using Table data type as a suggestion to lower Stack use. I'm > > just > > having trouble figuring out how to use the table var correctly. > > > > 1 update @MDMB > > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > > 3 from @MDMBTotaisAssoc BIT > > 4 where BIT.MRCSKA = '-' and > > 5 @MDMB.MRCUIC = BIT.MRCUIC > > > > I get an error in line 2: > > > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > > Line 2: Incorrect syntax near '.'. > > > > Thanks in advance > > > > > > > > > > > > > You MUST use an alias when referring a table variable in a JOIN.
See http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html Show quote "João Costa" <JooCo***@discussions.microsoft.com> wrote in message news:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... > hello, I'm using Table data type as a suggestion to lower Stack use. I'm > just > having trouble figuring out how to use the table var correctly. > > 1 update @MDMB > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > 3 from @MDMBTotaisAssoc BIT > 4 where BIT.MRCSKA = '-' and > 5 @MDMB.MRCUIC = BIT.MRCUIC > > I get an error in line 2: > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > Line 2: Incorrect syntax near '.'. > > Thanks in advance > > > Ok, I'm trying that, do you have some ideas on the sintax?
Show quote "Roji. P. Thomas" wrote: > You MUST use an alias when referring a table variable in a JOIN. > > See > http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html > > > -- > Roji. P. Thomas > Net Asset Management > https://www.netassetmanagement.com > > > "João Costa" <JooCo***@discussions.microsoft.com> wrote in message > news:977CB913-3E0F-48DD-AAD7-F4084A4B6857@microsoft.com... > > hello, I'm using Table data type as a suggestion to lower Stack use. I'm > > just > > having trouble figuring out how to use the table var correctly. > > > > 1 update @MDMB > > 2 set @MDMB.MDMBMT = @MDMB.MDMBMT - BIT.MDMBMT > > 3 from @MDMBTotaisAssoc BIT > > 4 where BIT.MRCSKA = '-' and > > 5 @MDMB.MRCUIC = BIT.MRCUIC > > > > I get an error in line 2: > > > > Server: Msg 170, Level 15, State 1, Procedure PCRMMRCQ00vInt, Line 2 > > Line 2: Incorrect syntax near '.'. > > > > Thanks in advance > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics