使用外键和主键进行更新 [英] Update with foreing key and primary key
问题描述
大家好！
我有一个存储过程可以更新和删除不同的行
表，但是当我尝试删除时主表显示问题
主键和外键。我把这些键掉了下来并在AD之后这些
键，但是我需要在一个存储过程中进行此操作但是在运行时显示问题
。
我可以在一个存储过程中进行此操作吗？
这是一个存储过程
创建过程EIS.SP_CUSTOMER_CLEANING（）
动态结果集1
----------------------------------- -------------------------------------
- SQL存储过程
------------------------------------------ ------------------------------
P1：BEGIN ATOMIC
FOR V1 AS
SELECT B.CLIENTID as IDCLIENTID，
B.MAPPEDCLIENTID as MAPPED
来自EIS。 CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID！= MAPPEDCLIENTID
DO
从EIS.CLI_BANK删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_ENROLLMENT删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_ENROLLMENTTRANSACTION删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_PREFERREDCUSTOMERNUMBER删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_GOLDENPOINTSHISTORY删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_CHECK删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_PHONE删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_EXTRAFIELD删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_ID删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_ID_PICTURE删除
WHERE CLIENTID = IDCLIENTID;
从EIS.CLI_WATCHLIST删除
WHERE CLIENTID = IDCLIENTID;
- 这是主表
ALTER TABLE EIS.CLI_CLIENT
DROP SQL051102111045710 CLIENTID;
从EIS.CLI_CLIEN删除T $ / $
WHERE CLIENTID = IDCLIENTID;
ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY（CLIENTID）;
-------------------------------------------- -----
- 更新表格
------------------------- ------------------------
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;
更新EIS.TXN_AGGREGATES AGREGATES
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;
结束;
结束P1
这是消息
DB21034E该命令作为SQL语句处理，因为它是
而不是
有效的命令行处理器命令。在SQL处理期间，它返回了
：
SQL0104N意外的令牌ALTER TABLE EIS.CLI_CLIENT被发现
跟随D = IDCLIENTID; " ;.预期的代币可能包括：
"< psm_labellable_stmt>"。 LINE NUMBER = 50。 SQLSTATE = 42601
SQL0104N意外的令牌ALTER TABLE EIS.CLI_CLIENT
发现在D = IDCLIENTID;
之后。预期的代币可能包括：< psm_labellable_stmt>。
如果有人可以帮助我请
问候!!!
谢谢
$ b $bIván
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
推荐答案
回答战术问题：
DECLARE txt VARCHAR（1000）;
....
SET txt =''ALTER TABLE ......'';
EXECUTE IMMEDIATE txt;
....
但是你正在做的事情非常粗糙。知道为什么你必须采用如此激烈的方法才会更有趣。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm。 com / software / data / ond ... ness / conf2006 /
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/
我必须做的是删除和更新不同的程序
表，因为这个程序必须由应用程序执行，这就是为什么
我试图这么做。我需要的是消除主要
表的客户，但问题出现在我的按键上。
有些建议很好被接受
谢谢
$ b $bIván
Serge Rielau写道：
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:
回答战术问题：
DECLARE txt VARCHAR（1000）;
...
SET txt =''ALTER TABLE ......'';
EXECUTE IMMEDIATE txt;
...
但是你正在做的事情非常粗糙。知道为什么你必须采用如此激烈的方法才会更有趣。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http：// www.ibm.com/software/data/ond...ness/conf2006/
Ivan写道：
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
您是否更新了钥匙？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
IOD会议
http://www.ibm.com/software/data /ond...ness/conf2006/
这篇关于使用外键和主键进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
