SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Update

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update

    Hi All,

    When I execute the following line of code to update a table, I get an error. Can someone please tell me what am I doing wrong here.

    Thanks

    update Counter
    set Matchcode = (Case When Select Flag.ID
    From Flag
    Inner Join Counter
    ON Flag.ID = Counter.ID
    and flag.flag_code = 'DEL' Then 'Flag DEL')

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your CASE expression doesn't have an END keyword

    plus, the database might be getting confused as to which copy of the Counter table is being joined or updated, so you might need to use an alias in the subquery

    what database is this? db2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937. I have one more question for you.
    Is there a way I can combine these two update statements?

    update Counter
    set Matchcode = (Case When Select Flag.ID
    From Flag
    Inner Join Counter Cntr
    ON Flag.ID = Cntr.ID and flag.flag_code = 'DEL' Then 'Flag DEL' End)

    set Matchcode = (Case When Select Flag.ID
    From Flag
    Inner Join Counter Cntr
    ON Flag.ID = Cntr.ID and flag.flag_code = 'MOAD' Then 'MOAD' End)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what database is this? i'm guessing here --
    Code:
    update Counter
       set Matchcode 
         = case ( select Flag.flag_code
                    from Flag
                  inner 
                    join Counter Cntr
                      on Flag.ID = Cntr.ID )
              when 'DEL'  then 'Flag DEL' 
              when 'MOAD' then 'MOAD'
            end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •