Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Newbie - How to update multiple rows with select statment

    Newbie to the SQL Server.

    UPDATE GOLDIE
    SET GOLDIE_ID = (SELECT *,
    SUBSTRING(GOLDIE_ID,1,
    CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
    THEN 0
    ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
    end) STRIPPED_COL
    FROM GOLDIE_ID)

    Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

    Immd help will be greatly appreciated.

    Pam

  2. #2
    Join Date
    Oct 2004
    Posts
    4

    Unhappy

    I am disappointed that all the experts here have no time for my basic question ...

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you need to write a query like

    update a
    set a.col = b.col
    from yourtbl a, (your select query) b
    where a.primarykey = b.primarykey
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Oct 2004
    Posts
    4
    Thanks for the reply.

    Thats too basic. Are you suggesting me to create new table and then associate the values as you quoted above ??

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

    in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

    here's my solution (note: i tested it, including all alpha and all numeric column values) --
    Code:
    update GOLDIE
      set GOLDIE_ID = 
        case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
             then left(GOLDIE_ID
                      ,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
             else 0 end
     where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    DROP TABLE Pam24
    GO
    
    CREATE TABLE Pam24 (
       Pam24id		INT		IDENTITY
       CONSTRAINT XPKPam24
          PRIMARY KEY (Pam24id)
    ,  thingie		VARCHAR(50)	NULL
       )
    
    INSERT INTO Pam24 (thingie)
       SELECT '123 Main Street' UNION ALL
       SELECT NULL UNION ALL
       SELECT '456Any Road' UNION ALL
       SELECT '' UNION ALL
       SELECT '789 My Place'
    
    UPDATE Pam24
       SET thingie = Left(thingie, PatIndex('%[^0-9]%', thingie) - 1)
       WHERE  thingie LIKE '[0-9]%'
    
    SELECT *
       FROM Pam24
    Note that the two patterns are different, in fact exact opposites. I dislike having two patterns, but it was better than any alternative I could think of on short notice.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, nice try, but if the string does not start with a number, the original spec (yes, i realize it's buried inside some non-functional sql) required that you reset the entire value to 0

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, now that i look at it more closely, the 0 was actually the length parameter of the substring function, so i think maybe it's supposed to reset all alpha-only strings to empty strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2004
    Posts
    4

    Thumbs up

    Quote Originally Posted by r937
    enigma just enjoys giving generic syntax which might solve your problem and letting you fit your problem to the generic syntax

    in this case, enigma, i think you need to actually try it yourself, and see whether your solution fits the stated problem

    here's my solution (note: i tested it, including all alpha and all numeric column values) --
    Code:
    update GOLDIE
      set GOLDIE_ID = 
        case when patindex('%[A-Z,a-z]%',GOLDIE_ID) > 1
             then left(GOLDIE_ID
                      ,patindex('%[A-Z,a-z]%',GOLDIE_ID)-1)
             else 0 end
     where patindex('%[A-Z,a-z]%',GOLDIE_ID) > 0
    Thanks r937. It does answers my question.

    Cheers

Posting Permissions

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