Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Unanswered: Updating table referencing 2nd table using case

    Hi

    Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...

    Using T-sql!!!

    update import set import.gone =
    from import
    inner join stat
    ON stat.id = import.id
    CASE
    WHEN stat.A = import.field2 THEN import.gone = sec.A
    WHEN stat.B = import.field2 THEN import.gone = sec.B
    WHEN stat.C = import.field2 THEN import.gone = sec.C
    WHEN stat.D = import.field2 THEN import.gone = sec.D
    WHEN stat.E = import.field2 THEN import.gone = sec.E
    WHEN stat.F = import.field2 THEN import.gone = sec.F
    ELSE import.gone = null
    END

    Any help would be greatly appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    update import set import.gone =
    CASE import.field2
    WHEN stat.A THEN sec.A
    WHEN stat.B THEN sec.B
    WHEN stat.C THEN sec.C
    WHEN stat.D THEN sec.D
    WHEN stat.E THEN sec.E
    WHEN stat.F THEN sec.F
    ELSE null
    END
    from import
    inner join stat
    ON stat.id = import.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    7
    Works like a treat - thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it does????

    i'm surprised, because there's no "sec" table in the FROM clause, it should have failed

    i was merely fixing your CASE expression, and i was expecting you to come back with another error

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

Posting Permissions

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