Results 1 to 4 of 4

Thread: using update

  1. #1
    Join Date
    Oct 2002
    Location
    London, uk
    Posts
    3

    Angry Unanswered: using update

    Hi,

    I am new to SQL and feel like I am beating my head against a wall. I am trying to update the contents of a column in my database using the 'update' statement. My code is:


    UPDATE t_asset SET fk_asset_status_id = (select a.asset_status_id
    from t_asset_statu a, t_asset_status b, t_assets c
    where a.asset_status_description = b.status
    and b.status_id = c.fk_status_id)

    However, I keep getting the following error:
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

    I know this is a common error, but at the moment can find no way around it.

    please help me,

    miggy

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Are you looking for this?
    Code:
    update c
       set c.fk_asset_status_id = a.asset_status_id
      from t_asset_statu a
      join t_asset_status b on a.asset_status_description = b.status
      join t_assets c on b.status_id = c.fk_status_id
    However you probably won't get the right answer, consider the result to the following code:
    Code:
    select a.asset_status_description as 'Key1', b.status as 'Key2', b.status_id as 'Key3', c.fk_status_id as 'Key4', c.fk_asset_status_id as 'OldValue', a.asset_status_id as 'NewValue'
      from t_asset_statu a
      join t_asset_status b on a.asset_status_description = b.status
      join t_assets c on b.status_id = c.fk_status_id
    What will be the value of c.fk_asset_status_id after your update?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    London, uk
    Posts
    3
    thanks Paul, I will try it and post how I got on later.

    cheers,

    miggy

  4. #4
    Join Date
    Oct 2002
    Location
    London, uk
    Posts
    3
    I worked it out a different way eventually, by using temp tables. thanks anyway Paul

Posting Permissions

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