Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: update a column from a different column value in the same table

    I'm trying to update a column 'dep' in tab1 by selecting amt from tab1 where type='f'
    I tried to insert it directly in the table but as the select statement returns multiple rows I was not able to do the insert.

    insert into tab1
    (
    col1,
    col2
    col3,
    ..
    ..
    amt,
    type,
    dep
    col4
    )
    select
    d.col1,
    tab3.col2,
    d.col3,
    d.amt,
    d.type,
    (select d.amt from tab2@mylink d where d.type='f'
    from tab2@mylink d),
    col4
    from tab2@mylink d, tab3
    where
    tab3.col1=d.col1 and
    tab3.col2=d.col2
    /
    Then I tried to update the table as follows but it still gives me the same error ORA 01427 at the select statement.
    Am I qualifying it the wrong way?

    update tab1 set tab1.dep=
    (select d.amt from tab2@mylink d
    where d.type='f' and
    tab1.col1=d.col1)

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    01427, 00000, "single-row subquery returns more than one row"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    try this:

    update tab1
    set tab1.dep=(select d.amt
    from tab2@mylink d
    where d.type='f'
    and tab1.col1=d.col1)
    where exists (select 1
    from tab2@mylink d
    where d.type='f'
    and tab1.col1=d.col1)

Posting Permissions

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