Update one column of each row in a table [PL/SQL, unix scripting]

Question

i have a table with 12 columns:

table1:

1	2	3	4	5	6	7	8	9	10	11	12
abc	1	000	aaa	zzz	2	234	OOO	00001	01	123	214
def	2	023	bbb	yyy	4	345	PPP	00002	02	133	224
ghi	3	011	ccc	xxx	6	456	QQQ	00003	03	143	234
jkl	4	112	ddd	www	8	567	RRR	00004	04	153	244

i would like to use 3rd column data in a loop and fetch 'best match' data from another table.

table2:

1	2	3	4
0	777	676	america
00	888	878	england
01	999	989	france
02	666	656	germany

3rd column data will be trimmed in the loop until a match in table2 is fetched.

first row:
iter 1: table1 row1 col3=000 -- no match in table
iter 2: table1 row1 col3=00 -- return england, replace table1 row1 col12=214 with 'england'

updated row: abc,1,000,aaa,zzz,2,234,OOO,00001,01,123,england


second row:
iter 1: table1 row2 col3=023 -- no match in table
iter 2: table1 row2 col3=02 -- return germany, replace table1 row1 col12=224 with 'germany'

updated row: def,2,023,bbb,yyy,4,345,PPP,00002,02,133,germany

Tilen · Accepted Answer · 2021-11-04 07:58:11Z

2

What you will need to do is create a procedure, then within the procedure declare a cursor as well as a variable_c_row cursor_name%ROWTYPE.

Within the procedure, this will be the contents:

OPEN cursor_name

FETCH cursor_name INTO variable_c_row;

WHILE cursor_name%FOUND LOOP
    -- Declare a number variable (i)
    i := 0;
    -- Declare a varchar variable (match)
    match := variable_c_row.col3
    
    WHILE length(match) > 0 LOOP OR l_countryname IS NULL
      begin
        -- Declare a yourrow%ROWTYPE variable (l_countryname)
        SELECT col4 FROM table2 INTO l_countryname WHERE col1 = match;
        
        UPDATE table1 SET col12 = l_countryname;
      exception when no_data_found then null;
      end;

      i := i+1;
      match := substr(variable_c_row.cow3, 0, length(match)-i);
    END LOOP;
  FETCH cursor_name INTO variable_c_row;
END LOOP;

CLOSE cursor_name;

Since the question had no DDL or DML, the most I can provide is a broad answer, which has not been tested.

edited Nov 4, 2021 at 7:58

answered Nov 4, 2021 at 7:37

Tilen

5313 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dcdum2018 Over a year ago

can you briefly explain these: variable_c_row.row3,l_yourrow, and the select row4 statement after begin? will try to add and run it in a shell script

Tilen Over a year ago

@dcdum2018 Declare a variable like variable_c_row cursor_name%ROWTYPE. This variable will create an array and you can access any of the columns in that row by using the variable and column separated by "." Correction on the l_yourrow, meant to say l_countryname (has been edited): Use l_countryname table2.row4%type in your declaration.

dcdum2018 Over a year ago

i think row4 is col4 here -> select col4 from table2 into l_countryname where row1 = match; where match := variable_c_row.row3 can you briefly explain this part? (e.g. what is row1?) thank you

Tilen Over a year ago

Correct. It's supposed to be col4. I wrote the code without testing it and got a bit lost it seems. It's supposed to say col3, not row3. It will get your col3. Also I've corrected some of the code.

dcdum2018 Over a year ago

thank you! i was able to test it. only minor changes: match := ... length(match)-1)

|

Collectives™ on Stack Overflow

Update one column of each row in a table [PL/SQL, unix scripting]

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related