populate blank columns using update statment

Question

I need some help writing update statement. My objective is to populate any blank EQ_ID and ORDERID by copying the EQ_ID and ORDERID from a row that has the same A_NUMBER, C_CD, and PERSONID

I require the update statement to scan the EST (name of the table) table:

For a certain period of time eg. 1st May 2015 to 10th May 2015
Look for a row with blank EQ_ID and ORDER_ID
populate any blank EQ_ID and ORDERID by copying the EQ_ID and ORDERID of a row that has the same A_NUMBER, C_CD, and PERSONID

Can you please let me know how to do this the best way?

Attached is a screenshot of data from EST table Example table data

If you're using Oracle, googling "Update join" yields this as the second hit: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join — David Hoelzer, May 25 '15 at 13:47

score 3 · Accepted Answer · answered May 25 '15 at 13:48

You can do this using update and a correlated subquery. Oracle allows you to set multiple columns from the same subquery:

update est
    set (eq_id, order_id) = (select est2.eq_id, est2.order_id
                             from est est2
                             where est2.a_number = est.a_number and
                                   est2.c_cd = est.c_cd and
                                   est2.personid = est.personid and
                                   rownum = 1
                            )
    where eq_id = 0 and orderid = 0;

You say "blank" in the text but the data shows 0. The outer where clause may need to be modified to suit your actual data.

thanks Gordon, this method seems easier and makes the most sense to me. — D. Ace, May 25 '15 at 21:16

score 1 · Answer 2 · answered May 25 '15 at 13:53

Psuedo Code:

Select distinct A_NUMBER, C_CD, PERSONID 
     into temp_table from TableName 
     where EQ_ID = 0 and Order_ID = 0

-- Choose correct data types
declare @Current_ANumber varchar(100)
declare @Current_C_CD varchar(100)
declare @Current_PERSONID varchar(100)
declare @Current_EQ_ID varchar(100)
declare @Current_Order_ID varchar(100)

select @Current_ANumber = A_NUMBER, @Current_C_CD= C_CD, @Current_PERSONID= PERSONID from temp_table
while (@Current_ANumber is not null)
begin
     select distinct @Current_EQ_ID = EQ_ID, @Current_Order_ID = Order_ID where EQ_ID > 0 and Order_ID > 0
     update table_name set EQ_ID = @Current_EQ_ID, Order_ID = @Current_Order_ID
          where A_NUMBER = @Current_ANumber 
               and C_CD = @Current_C_CD 
               and PERSONID = @Current_PERSONID
               and EQ_ID = 0
               and Order_ID = 0

     delete from temp_table 
          where A_NUMBER = @Current_ANumber 
               and C_CD = @Current_C_CD 
               and PERSONID = @Current_PERSONID

     set @Current_ANumber = null                   
     set @Current_C_CD = null                   
     set @Current_PERSONID= null                   
     select @Current_ANumber = A_NUMBER, @Current_C_CD= C_CD, @Current_PERSONID= PERSONID from temp_table
end

I work with SQL Server, so while this code may not work out of the box for Oracle, it should put you on the right path.

populate blank columns using update statment

2 Answers2