By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,279 Members | 1,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,279 IT Pros & Developers. It's quick & easy.

Update with foreing key and primary key

P: n/a
Hi to all !!!

I have one stored procedure that update and delete rows in differents
tables, but when I try of delete of the main table this show problems
with primary and foreign key. I DROP these keys and after AD these
keys, but I need make this in one stored procedure but show problems
when run this.
I can make this in one stored procedure?

This is the estored procedure

CREATE PROCEDURE EIS.SP_CUSTOMER_CLEANING ()
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------

P1: BEGIN ATOMIC

FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_ENROLLMENT
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_ENROLLMENTTRANSACTION
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_PREFERREDCUSTOMERNUMBER
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_GOLDENPOINTSHISTORY
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CHECK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_PHONE
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_EXTRAFIELD
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_ID
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_ID_PICTURE
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_WATCHLIST
WHERE CLIENTID = IDCLIENTID;

-- THIS IS THE MAIN TABLE

ALTER TABLE EIS.CLI_CLIENT
DROP SQL051102111045710 CLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;

ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID) ;
-------------------------------------------------
-- UPDATE TABLES
-------------------------------------------------

UPDATE EIS.CLI_HISTORY HISTORY
SET HISTORY.CLIENTID = MAPPED,
ISMERGED = 2,
HISTORY.TIMESTAMP = TIMESTAMP(generate_unique())
WHERE HISTORY.CLIENTID = IDCLIENTID;

UPDATE EIS.TXN_CLIENT TXN_CLIENT
SET TXN_CLIENT.CLIENTID = MAPPED
WHERE TXN_CLIENT.CLIENTID = IDCLIENTID;

UPDATE EIS.TXN_AGGREGATES AGGREGATES
SET AGGREGATES.CLIENTID = MAPPED
WHERE AGGREGATES.CLIENTID = IDCLIENTID;

UPDATE EIS.TXN_COMPLIANCEISSUE COMPLIANCEISSUE
SET COMPLIANCEISSUE.CLIENTID = MAPPED
WHERE COMPLIANCEISSUE.CLIENTID = IDCLIENTID;

UPDATE EIS.CLI_MAPING CLI_MAPING
SET STATUSID = 3
WHERE CLI_MAPING.CLIENTID = IDCLIENTID;

END FOR;

END P1

And this is the message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "ALTER TABLE EIS.CLI_CLIENT " was found
following "D = IDCLIENTID; ". Expected tokens may include:
"<psm_labellable_stmt>". LINE NUMBER=50. SQLSTATE=42601

SQL0104N An unexpected token "ALTER TABLE EIS.CLI_CLIENT
" was found following "D = IDCLIENTID;

". Expected tokens may include: "<psm_labellable_stmt>".
If somebody can help me please
Greetings!!!

Thanks

Iván

Sep 6 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
To answer the tactical question:
DECLARE txt VARCHAR(1000);
....
SET txt = 'ALTER TABLE ......';
EXECUTE IMMEDIATE txt;
....

But this is pretty crude what you are doing. It woudl be more
interesting to know WHY you have to resort to such drastic methods.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #2

P: n/a
What I must do is a procedure that delete and updates the different
tables, since this procedure must be executed by an application, is why
I am trying to do it thus. I need is to eliminate clients of the main
table but the problem appears me by the keys.

Some suggestion well is accepted

Thanks
Iván
Serge Rielau wrote:
To answer the tactical question:
DECLARE txt VARCHAR(1000);
...
SET txt = 'ALTER TABLE ......';
EXECUTE IMMEDIATE txt;
...

But this is pretty crude what you are doing. It woudl be more
interesting to know WHY you have to resort to such drastic methods.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #3

P: n/a
Ivan wrote:
What I must do is a procedure that delete and updates the different
tables, since this procedure must be executed by an application, is why
I am trying to do it thus. I need is to eliminate clients of the main
table but the problem appears me by the keys.

Some suggestion well is accepted
Are you updating the keys?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #4

P: n/a
I delete the key for example

ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT SQL051102111045710;

ADND AFTER

ALTER TABLE EIS.CLI_CLIENT ADD CONSTRAINT SQL051102111045710 PRIMARY
KEY (CLIENTID) ;

I made one test, in steps separated, it was execute the DROP key after
execute the stored procedure and the last step ADD the key, But I need
that I do this in a stored procedure
I made the test with

DECLARE TXT VARCHAR(1000);
....
SET TXT = 'ALTER TABLE EIS.CLI_CLIENT
DROP SQL051102111045710';
EXECUTE IMMEDIATE TXT;
......

And show the same message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SP_CUSTOMER_CLEANING" was found
following
"CREATE EIS.PROCEDURE". Expected tokens may include: "<space>".
SQLSTATE=42601

SQL0104N An unexpected token "SP_CUSTOMER_CLEANING" was found
following "CREATE EIS.PROCEDURE". Expected tokens may include:
"<space ".

Explanation:

Thanks for answer
Iván
Serge Rielau wrote:
Ivan wrote:
What I must do is a procedure that delete and updates the different
tables, since this procedure must be executed by an application, is why
I am trying to do it thus. I need is to eliminate clients of the main
table but the problem appears me by the keys.

Some suggestion well is accepted
Are you updating the keys?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #5

P: n/a
CREATE EIS.PROCEDURE ???
Now think about that one for a moment...
Aside I know you DROP the key.
What I want to know is whether you update the key values in your UPDATE
statement.
I think you are trying to fix something that you shouldn't have broken
to begin with.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #6

P: n/a
Yes update the key values.
I have created a new procedure, but it presents/displays error to me
when I call it

Create Procedure DeleteUpdate()
begin
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
DROP CONSTRAINT SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;

FOR V1 AS
SELECT B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
END FOR;
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end

But show the next message

SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement
is blank or empty. SQLSTATE=42617

SQL0198N The statement string of the PREPARE or EXECUTE IMMEDIATE
statement is blank or empty.
Iván

Serge Rielau wrote:
CREATE EIS.PROCEDURE ???
Now think about that one for a moment...
Aside I know you DROP the key.
What I want to know is whether you update the key values in your UPDATE
statement.
I think you are trying to fix something that you shouldn't have broken
to begin with.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 6 '06 #7

P: n/a
Strings can't cross lines:
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
DROP CONSTRAINT SQL051102111045710';

Try:
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT'
|| 'DROP CONSTRAINT SQL051102111045710';
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 7 '06 #8

P: n/a
Thanks Serge

I used SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT'
|| 'DROP CONSTRAINT SQL051102111045710';

and was successful, but when I call the stored procedure show me
problems of keys, then it reviews the table that I am delete the keys,
then updates it from Control Center and if it had delete it. How I can
make so that it refreshes to me the keys deletes and with it it can
execute the stored procedure.

I try with this

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT'
||'DROP CONSTRAINT SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
REFRESH TABLE EIS.CLI_CLIENT;
COMMIT;

and change ";" for "," but show diferents problems

Show this message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "REFRESH" was found following "IMMEDIATE
txtdrop; ". Expected tokens may include: "DROP". LINE NUMBER=10.
SQLSTATE=42601

SQL0104N An unexpected token "REFRESH" was found following "IMMEDIATE
txtdrop;
". Expected tokens may include: "DROP

Iván

Serge Rielau wrote:
Strings can't cross lines:
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
DROP CONSTRAINT SQL051102111045710';

Try:
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT'
|| 'DROP CONSTRAINT SQL051102111045710';
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 7 '06 #9

P: n/a
Ivan wrote:
Thanks Serge

I used SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT'
|| 'DROP CONSTRAINT SQL051102111045710';

and was successful, but when I call the stored procedure show me
problems of keys, then it reviews the table that I am delete the keys,
then updates it from Control Center and if it had delete it. How I can
make so that it refreshes to me the keys deletes and with it it can
execute the stored procedure.

I try with this

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT'
||'DROP CONSTRAINT SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
REFRESH TABLE EIS.CLI_CLIENT;
COMMIT;

and change ";" for "," but show diferents problems
?? Why would you do that??
I admit I'm entirely lost on what you're trying to do.
Why replace a valid delimiter with a made up illegal one?
You will also get a -104 if you replace DROP with BLURP... same thing.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 7 '06 #10

P: n/a
I what I must do it is to delete clients of a table, but this it does
not leave me by the keys, then what try to do is, first to delete the
keys later to delete the clients of the table and after create the
keys.

Thanks
Iván

Serge Rielau wrote:
Ivan wrote:
Thanks Serge

I used SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT'
|| 'DROP CONSTRAINT SQL051102111045710';

and was successful, but when I call the stored procedure show me
problems of keys, then it reviews the table that I am delete the keys,
then updates it from Control Center and if it had delete it. How I can
make so that it refreshes to me the keys deletes and with it it can
execute the stored procedure.

I try with this

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT'
||'DROP CONSTRAINT SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
REFRESH TABLE EIS.CLI_CLIENT;
COMMIT;

and change ";" for "," but show diferents problems
?? Why would you do that??
I admit I'm entirely lost on what you're trying to do.
Why replace a valid delimiter with a made up illegal one?
You will also get a -104 if you replace DROP with BLURP... same thing.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 7 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.