need to save an id in sql script to delete later

I have a sql script that is attempting to clean up my database after I run a test. I have some foreign key constraints that I need to work around.

I need to capture an id (addmasId)  from table channel,
then delete the row from channel
then delete a row from table address using the variable I set to capture addmasId.
Here is an example of what I attempted to do:

select @addmas1 = addmasId from address where addmasId in (select addmasId from channel where chamasId = &channelId);
 
delete from channel where chamasId = &channelId;

delete from address where addmasId = @addmas1;
commit;

I am getting the following error:

Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
atxmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>select @addmas1 = addmasId from address

This isn't Oracle syntax.

You can probably use variables in a sqlplus script if you want to but can't you just do two deletes?

something like:
delete from channel where chamasId = &channelId;
delete from address where addmasId in (select addmasId from channel where chamasId = &channelId);
commit;

Open in new window


If the above doesn't work, I might create a pl/sql block or stored procedure to do the first select and then the deletes.
0
johnsoneSenior Oracle DBACommented:
Your logic seems backward.  You are passing in a id that is related to the child table and then trying to delete the parent that is associated with the child.  You can do that, but what if the parent has other children?  Or, are your deletes backward and CHANNEL is the parent and ADDRESS is the child.
1
slightwv (䄆 Netminder) Commented:
I do agree that things seem a bit odd but you know your data.

Having re-read what I posted, I'm pretty sure it won't do what you need.

If I copy the original logic you posted, here is the sqlplus syntax that should do the same thing.

It is untested but should be close.
var v_addmasid number
exec select addmasId into :v_addmasid from address where addmasId in (select addmasId from channel where chamasId = &channelId);
 
delete from channel where chamasId = &channelId;
exec delete from address where addmasId = :v_addmasid;
commit;

Open in new window


If it is just a FK issue, I might look at deferrable constraints.  These don't get checked until a commit.  That might work for you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atxmanAuthor Commented:
This helped get me on the right track
0
flow01Commented:
If you use sqlplus you can also use the new_value option of sqlplus column command to define and assign a value
 
column addmasid new_value addmasid
select 12 addmasid from dual;
select object_name from all_objects where rownum < &ADDMASID;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.