SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transact-SQL UPDATE query

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

    Code:
    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.
    Read my blog: The (not so) Daily Me

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ...WHERE [sRep ID] Is Null

    '' and NULL are not the same thing

  3. #3
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    ...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.
    Read my blog: The (not so) Daily Me

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If it contains a blank string, then use
    =''
    Null would fail, in this case

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help so far, but it's still erroring out.

    Here's my current query:

    Code:
    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'."
    Read my blog: The (not so) Daily Me

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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)]

  8. #8
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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)]
    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)"
    Read my blog: The (not so) Daily Me

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look closer, oh ye of the long neck, the alias was subtley different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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.
    Read my blog: The (not so) Daily Me

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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?

  12. #12
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    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).

    Code:
    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.
    Read my blog: The (not so) Daily Me

  13. #13
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Read my blog: The (not so) Daily Me

  14. #14
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anybody?

    I have an urgent deadline on this...
    Read my blog: The (not so) Daily Me

  15. #15
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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

  16. #16
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  17. #17
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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'
    No. That's sample data of tens of thousands of rows.
    Read my blog: The (not so) Daily Me

  18. #18
    SitePoint Zealot
    Join Date
    Jul 1999
    Location
    Virginia, USA
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had to go ahead and just write it in VB:

    Code:
     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.
    Read my blog: The (not so) Daily Me

  19. #19
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, best wishes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •