Home » SQL & PL/SQL » SQL & PL/SQL » Loop Help
Loop Help [message #187965] Wed, 16 August 2006 09:31 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I can write a simple loop, however I have a task at hand that I need some help with.

I have two tables:
Table A, Table B
I have to compare two columns between the tables, say code1 and code2.

What I wanted to do and I have tried several different things and they have not worked:

In Table A go to the first record, then loop through Table B and see if code1 and code2 match a record between the two. If a record matches then Update a flag in Table A, otherwise perform other checks and then update Table A a different way.

I can use a case statement for the updates which is no problem, the problem I am having is getting the loops correctly coordinate with one another. I hope this makes sense.

Any help would be appreciated.
Re: Loop Help [message #187967 is a reply to message #187965] Wed, 16 August 2006 09:33 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Please provide create table and insert scripts along with what you have tried.

Jim
Re: Loop Help [message #187969 is a reply to message #187967] Wed, 16 August 2006 09:53 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
create table table_a
(code_1 varchar2(6),
code_2 varchar2(6),
status varchar2(1));

create table table_b
(code_1 varchar2(6),
code_2 varchar2(6))

Insert into table_a
values('xxxxxx','yyyyyy',null);

Insert into table_a
values('xxxxxy','yyyyyx', null);

Insert into table_a
values('xxxyyy','yyyxxx',null);

Insert into table_b
values('xxxxxx','yyyyyy');

Insert into table_b
values('xxxxxy','zzzzzz');

Insert into table_b
values('zzzzzz','yyyxxx');



declare
cursor c1 is select code_1,code_2, rowid
from table_a;

cursor c2 is select code_1 ,code_2
from table_b;
v_code_1 varchar2(6);
v_code_2 varchar2(6);

begin
for c1rec in c1 loop
v_code_1 := c1rec.code_1;
v_code_2 := c1rec.code_2;
for c2rec in c2 loop
Case when v_code_1 = c2rec.code_1 and v_code_2=c2rec.v_code_1 then
update table_a set status = 'A' where rowid = c1rec.rowid;
else update table_b set status = 'D' where rowid = c1rec.rowid;
end case;
end loop;
end loop;
commit;
end;
Re: Loop Help [message #187972 is a reply to message #187969] Wed, 16 August 2006 10:03 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Nice one, thanks for that. Now, based on that data that you have provided, the procedure makes no sense. For a start, Your procedure is trying to update a column in table_b that doesn't exist (according to your create table statements.) This looks like it should be getting done with a couple of simple updates.
as a final clarification, can you post what your expected results should be?

Thanks.
Jim

[Updated on: Wed, 16 August 2006 10:04]

Report message to a moderator

Re: Loop Help [message #187973 is a reply to message #187972] Wed, 16 August 2006 10:07 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
And furthermore, in your first post you mention some value should be between some others, but in your procedure you only check if values are equal. So, we could use some clarification on that too.

Regards,
Sabine
Re: Loop Help [message #187975 is a reply to message #187973] Wed, 16 August 2006 10:19 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I looked over the first post and the only between is the tables... not between values.

And the updating status in table_b was a typo sorry, should be table a(I was ommiting actual table names for security reasons)

Ok Original Data:

Table A:
code_1, code_2, status
_______________________
xxxxxx,yyyyyy,
xxxxxy,yyyyyx,
xxxyyy,yyyxxx,

Table B:

xxxxxx,yyyyyy
xxxxxy,zzzzzz
zzzzzz,yyyxxx

Result:
Table A:
code_1, code_2, status
_______________________
xxxxxx,yyyyyy,A <=== because both fields match a record in table b
xxxxxy,yyyyyx,D <=== because only one field matches a record in table b
xxxyyy,yyyxxx,D <=== because only one field matches a record table b

Actually there will be other checks to populate a reason field not contained here but I can figure that out, getting one loop from another is the problem i am having.

Thanks again for you guys input.



[Updated on: Wed, 16 August 2006 10:22]

Report message to a moderator

Re: Loop Help [message #187978 is a reply to message #187975] Wed, 16 August 2006 10:28 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry about the between, I misread that sentence...


Maybe no loop is needed, just plain SQL:

UPDATE table_a a
SET    a.status = 'A'
WHERE  EXISTS (SELECT 1
        FROM   table_b b
        WHERE  a.code_1 = b.code_1
               AND a.code_2 = b.code_2)


(or variations on this theme, for values D etc.)
Regards,
Sabine
Re: Loop Help [message #187983 is a reply to message #187978] Wed, 16 August 2006 10:39 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
or

update table_a a
set a.status = nvl((select 'A'
from table_b b
where a.code_1 = b.code_1
and a.code_2 = b.code_2),'D')


Re: Loop Help [message #187989 is a reply to message #187983] Wed, 16 August 2006 11:03 Go to previous message
biohazardbill
Messages: 57
Registered: March 2006
Member
Well actually I kinda did it that way(just to get it done), however let's add to the equation.

If code_1 and code_2 do not match then the status code is set to 'D', now we need a reason which is where the other checks i was talking about come in... if code_1 from table a exists in table_b but not code_2, then populate reason(not in the current table structure) to reason 1, if code_2 exists in table_b but not code_1, then populate reason to reason 2, if neither exist in table_b then populate reason with reason 3. This is why I thought a loop scenario would work well for I could do all the processing at once instead of multiple updates.
Previous Topic: Export data to microsoft access
Next Topic: select only duplicate values.............
Goto Forum:
  


Current Time: Thu Dec 08 06:33:01 CST 2016

Total time taken to generate the page: 0.24235 seconds