Transact-SQL UPDATE query

[starrsoft]

What's the matter with the following SQL Update query:

UPDATE [CommissionReport0555 (LY)] AS [uCommissionReport0555 (LY)]
   SET [uCommissionReport0555 (LY)].[Rep ID]=(SELECT [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 [sRep ID]=''

Basically, the table looks like 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

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

[longneck]

...WHERE [sRep ID] Is Null

'' and NULL are not the same thing

[starrsoft]

...WHERE [sRep ID] Is Null

'' and NULL are not the same thing

Point taken. And I was aware of that. However, I believe that that field contains a blank string rather than a NULL. However it is, it's immaterial to this update query erroring out.

[afrika]

If it contains a blank string, then use

=''

Null would fail, in this case

[r937]

most likely the subquery is returning more than one row

in the subquery, instead of

= ( SELECT [Rep ID] AS [sRep ID]

try

= ( SELECT min([Rep ID]) AS [sRep ID]

also, the WHERE clause for the outer query has WHERE [sRep ID]='' and the problem with this is that this column alias is not defined in the outer query

[starrsoft]

Thanks for the help so far, but it's still erroring out.

Here's my current query:

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]=''

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

[longneck]

well, if you had posted the error message in your first post, we could have addressed the problem correctly in the first post. why are you aliasing a table using the same name? just get rid of AS [uCommissionReport0555 (LY)]

[starrsoft]

well, if you had posted the error message in your first post, we could have addressed the problem correctly in the first post. why are you aliasing a table using the same name? just get rid of AS [uCommissionReport0555 (LY)]

My humble apologies. It slipped my mind.

--

Now a question: The reason that I aliased the table when it is invoked in the SELECT is so that the tables (which are merely two instances of the same table) from the two queries (UPDATE and SELECT) would be distinguishable. How are they going to be distinguishable now for the WHERE clause of the SELECT subquery? "[Rep]=LEFT([uCommissionReport0555 (LY)].[Rep], LEN([uCommissionReport0555 (LY)].[Rep])-6)"

[r937]

look closer, oh ye of the long neck, the alias was subtley different

[starrsoft]

look closer, oh ye of the long neck, the alias was subtley different

Yup. You will notice that all aliases are prefixed with a single letter corresponding to the query type that the table/field instance is from.

[longneck]

look closer, oh ye of the long neck, the alias was subtley different

ah yes, i noticed that shortly after i posted. i retract that statement. might i suggest that since you're aliasing the table, use something less complicated? perhaps something that doesn't have spaces and parens?

[starrsoft]

ah yes, i noticed that shortly after i posted. i retract that statement. might i suggest that since you're aliasing the table, use something less complicated? perhaps something that doesn't have spaces and parens?

Sure (that was hardly my first choice for a table name, but the table names are automatically generated from another table of telco carriers and are looked up the same way).

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

Same error.

[starrsoft]

Let me explain my goal more clearly and perhaps y'all can tell me of a better approach altogether. 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]

Anybody?

I have an urgent deadline on this...

[swampBoogie]

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

[afrika]

First question:
Is it only two records to be changed ?

If so, then simple run this two scripts

1. update [table] set [rep ID] = 12345 where rep = 'M Mast Total'

2. update [table] set [rep ID] = 54321 where rep = 'MIB Total'

[starrsoft]

First question:
Is it only two records to be changed ?

If so, then simple run this two scripts

1. update [table] set [rep ID] = 12345 where rep = 'M Mast Total'

2. update [table] set [rep ID] = 54321 where rep = 'MIB Total'

No. That's sample data of tens of thousands of rows.

[starrsoft]

I had to go ahead and just write it in VB:

 If srStyle = "78294" Then
     rs.Open "Select * from [" & TableName & "] WHERE [Temp]=0 AND RIGHT([Rep], 5)='Total' AND LEFT([Rep], 5)<>'Grand'", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
     While Not rs.EOF
         rs("Rep ID") = DLookup("[Rep ID]", "[" & TableName & "]", "[Rep]='" & Left(rs!REP, Len(rs!REP) - 6) & "' AND [Temp]=0")
         rs.MoveNext
     Wend
     rs.Close
 End If

I'd obviously much rather have the above executing server-side (with my server's monster procs) via SQL than client-side via VB, but the VB had to do as a stopgap. I am testing swampBoogie's SQL code right now and if it works, I will definitely go with that instead.

[afrika]

ok, best wishes