#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10

    Need help with sql update..


    Hi,

    It seems like sql doesn't allow to use prefixes/aliases for update sentences.

    How can I make this sql work?:

    Code:
    update table1 b 
    set b.translation = (select a.translation from table1 a where a.translation_index=b.translation_index and a.language_code='EN')
    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    are you sure you want to update the table from itself?
    Code:
    update table1
       set translation = a.translation
      from table1 b
    inner
      join table2 a
        on b.translation_index = a.translation_index
     where a.language_code='EN'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by Dag
    Hi,

    It seems like sql doesn't allow to use prefixes/aliases for update sentences.

    How can I make this sql work?:

    Code:
    update table1 b 
    set b.translation = (select a.translation from table1 a where a.translation_index=b.translation_index and a.language_code='EN')
    Thanks
    Hello there,
    try this way,

    update b
    set
    b.translation_index =
    (select a.translation_index from #table1 a
    where
    a.translation_index=b.translation_index
    and
    a.language_code='EN')
    from #table1 b

IMN logo majestic logo threadwatch logo seochat tools logo