Update a column in one table with value 'Y',if the record exists in both the table,otherwise 'N'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Update a column in one table with value 'Y',if the record exists in both the table,otherwise 'N'

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-27 : 11:44:59

Ashok writes "Hi,
I got one problem .i don't how to solve it.I am describing the problem.

It will enter 'Y' into EXIST_FL column of Z_GLMOTMNT_AO2,IF SAME org_id AND acct_id exists IN both THE TABLES(org_acct AND Z_GLMOTMNT_AO2 ).

It will enter 'N' into EXIST_FL column of Z_GLMOTMNT_AO2,IF SAME org_id AND acct_id does not exist IN both THE TABLES(org_acct AND Z_GLMOTMNT_AO2 ).

I have wriiten this sql statement.But this not helping me.
UPDATE Z_GLMOTMNT_AO2 SET Z_GLMOTMNT_AO2.EXISTS_FL='Y'
WHERE Z_GLMOTMNT_AO2.ORG_ID='1.1.120' AND Z_GLMOTMNT_AO2.ACCT_ID IN (SELECT ORG_ACCT.ACCT_ID FROM ORG_ACCT WHERE ORG_ACCT.ORG_ID = '1.1.120') AND Z_GLMOTMNT_AO2.ROWVERSION=9297 AND Z_GLMOTMNT_AO2.MODIFIED_BY='CPSUPERUSER'

Down I am providing the Table strucures.

ORG_ACCT
----------
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ORG_ID                                    NOT NULL VARCHAR2(20)
 ACCT_ID                                   NOT NULL VARCHAR2(15)
 ACTIVE_FL                                 NOT NULL VARCHAR2(1)
 FY_CD_FR                                           VARCHAR2(6)
 PD_NO_FR                                           NUMBER(5)
 FY_CD_TO                                           VARCHAR2(6)
 PD_NO_TO                                           NUMBER(5)
 MODIFIED_BY                               NOT NULL VARCHAR2(20)
 TIME_STAMP                                NOT NULL DATE
 RQ_APPR_PROC_CD                                    VARCHAR2(8)
 ROWVERSION                                         NUMBER(10)

Z_GLMOTMNT_AO2
--------------------
Name                                      Null?    Type
----------------------------------------- -------- --------------
ORG_ID                                             VARCHAR2(20)
ACCT_ID                                            VARCHAR2(15)
ACTIVE_FL                                          VARCHAR2(1)
FY_CD_FR                                           VARCHAR2(6)
PD_NO_FR                                           NUMBER(5)
FY_CD_TO                                           VARCHAR2(6)
PD_NO_TO                                           NUMBER(5)
EXISTS_FL                                          VARCHAR2(1)
RQ_APPR_PROC_CD                                    VARCHAR2(8)
MODIFIED_BY                                        VARCHAR2(20)
TIME_STAMP                                         DATE
ROWVERSION                                         NUMBER(10)

any response would be appreciated.

regards
Ashok"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-27 : 17:11:14

UPDATE
Z_GLMOTMNT_AO2
SET
EXIST_FL = CASE WHEN EXISTS (SELECT org_id FROM org_acct A WHERE A.org_acct = Z_GLMOTMNT_AO2.org_acct AND A.acct_id = Z_GLMOTMNT_AO2.acct_id) THEN 'Y' ELSE 'N' END

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources