update column in table with select from same table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: update column in table with select from same table

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I want to update the column in a table using a select from the same table.
    This is the sql I am trying:
    update alpha_transactions a
    set a.purch_datetime=(select to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
    FROM ALPHA_TRANSACTIONS b
    where b.purch_datetime is null
    and a.txn_key=b.txn_key)
    /
    and it doens't work. It sets the column purch_datetime to null.
    The
    (select to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
    FROM ALPHA_TRANSACTIONS b
    where b.purch_datetime is null
    does bring back the correct date but when I add the update and where a.txn_key=b.txn_key it doesn't work. What is the correct syntax? Thanks.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    can you explain what are you trying to do? Is TXN_KEY primary key (or unique) in this table?

    IF TXN_KEY is PK you don't need any additional select, just do:

    update alpha_transactions
    set purch_datetime= to_date(lpad(day,2,'0')||'/'||lpad(month,2,'0')||'/'||year||' '||lpad(hours,2,'0')||':'||lpad(minutes,2,'0'),'DD/MM/YYYY HH24:MI')
    where....

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    Thanks.

Posting Permissions

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



Click Here to Expand Forum to Full Width