UPDATE query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE query

Author

Topic

starrsoft
Starting Member

4 Posts

Posted - 2006-01-18 : 11:34:18

UPDATE [CommissionReport0555 (LY)] AS [uCommissionReport0555 (LY)]
SET [uCommissionReport0555 (LY)].[Rep ID]=(SELECT min([Rep ID]) AS [sRep ID]  FROM [CommissionReport0555 (LY)] WHERE [Rep]=LEFT([uCommissionReport0555 (LY)].[Rep], LEN([uCommissionReport0555 (LY)].[Rep])-6) AND RIGHT([Rep],5) <> 'total')
WHERE [Rep ID] is null

And I'm getting an error of: "ADO Error: Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'WHERE'."

This query is trying to replace the NULLs with the proper Rep ID that corresponds to the proper Rep.

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-18 : 11:57:24

That's a pretty bizzare update...why aren't you using stored procedures btw?



UPDATE [CommissionReport0555 (LY)]
   SET [Rep ID] = (SELECT MIN([Rep ID])
		     FROM [CommissionReport0555 (LY)] 
  		    WHERE [Rep]=LEFT([Rep], LEN([Rep])-6) 
		      AND RIGHT([Rep],5) <> 'total')
 WHERE [Rep ID] is null

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

starrsoft
Starting Member

4 Posts

Posted - 2006-01-18 : 13:57:47

I am now getting: "Invalid length parameter passed to the substring function."

But I have an additional question: You removed my aliases which means that the "WHERE [Rep]=LEFT([Rep], LEN([Rep])-6)" can't distinguish between the two instances of the tables. How is that supposed to work?

Let me explain my problem more clearly. I need an UPDATE that changes this:

ID| Rep ID | Rep
-----------------------------------------
1 | NULL | M Mast Total
2 | 12345 | M Mast
3 | 12345 | M Mast
4 | NULL | MIB Total
5 | 54321 | MIB
6 | 54321 | MIB

to this:

ID| Rep ID | Rep
-----------------------------------------
1 | 12345 | M Mast Total
2 | 12345 | M Mast
3 | 12345 | M Mast
4 | 54321 | MIB Total
5 | 54321 | MIB
6 | 54321 | MIB

starrsoft
Starting Member

4 Posts

Posted - 2006-01-18 : 14:00:06

quote:
That's a pretty bizzare update...why aren't you using stored procedures btw?

Because the above query is actually dynamically generated by VB code.

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-18 : 15:52:27

OK, how about this


SELECT MIN([Rep ID])
  FROM  [CommissionReport0555 (LY)] a
  JOIN	[CommissionReport0555 (LY)] b
    ON a.[key] = b.[key]
 WHERE b.[Rep]=LEFT(b.[Rep], LEN(b.[Rep])-6) 
   AND RIGHT(a.[Rep],5) <> 'total')
   AND a.[Rep ID] is null

And I don't care if it's generated or not, can you explain why you would have a predicate lik ethat?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

starrsoft
Starting Member

4 Posts

Posted - 2006-01-18 : 17:15:42

quote:

Originally posted by X002548

OK, how about this


SELECT MIN([Rep ID])
  FROM  [CommissionReport0555 (LY)] a
  JOIN	[CommissionReport0555 (LY)] b
    ON a.[key] = b.[key]
 WHERE b.[Rep]=LEFT(b.[Rep], LEN(b.[Rep])-6) 
   AND RIGHT(a.[Rep],5) <> 'total')
   AND a.[Rep ID] is null

And is that supposed to go instead the parentheses of the UPDATE query? Because it errors out. Incorrect syntax.

I'm thinking that we still aren't on the same page here. In the above query, you did a "b.[Rep]=LEFT(b.[Rep], LEN(b.[Rep])-6)" when it should have been a comparison between the update instance of the table and the select instance of the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources