CODEWITHSUNDEEP
sqlsql-server
Ahsen
Ahsen

Reputation: 386

check the presence of a value of a column in another table sql

I'm new in SQL Server. I've to copy values of a column from table A to another table B with respect to another column(JOIN) But before copying I have to check whether this value exists in another table C. If yes then copy, otherwise return the records whose values are not in table C. my Query is;

IF EXISTS (SELECT Branch_ID FROM ADM_Branch
INNER JOIN UBL$ on ADM_Branch.Branch_Code = UBL$.[Branch Code  ] 
 WHERE ADM_Branch.Branch_Code = [UBL$].[Branch Code] )

        UPDATE EMP_Personal
    SET Account_Number = UBL$.[Account ] , Bank_ID = 1 , Branch_ID = (select Branch_ID from ADM_Branch join UBL$ on ADM_Branch.Branch_Code = UBL$.[Branch Code  ] where EMP_Personal.Emp_ID = UBL$.[Employee ID  ])
    FROM EMP_Personal JOIN UBL$
    ON EMP_Personal.Emp_ID = UBL$.[Employee ID  ]


ELSE

    ( SELECT UBL$.[Employee ID  ],UBL$.[Name ],UBL$.[Account ],UBL$.[Branch Code  ]
    FROM UBL$) except ( SELECT UBL$.[Employee ID  ],UBL$.[Name ],UBL$.[Account ],UBL$.[Branch Code  ]
    FROM UBL$
    right join ADM_Branch on  ADM_Branch.Branch_Code = UBL$.[Branch Code  ])

Upvotes: 2

Views: 2264

Answers (3)

Sonam
Sonam

Reputation: 3466

I think following code would give you some idea, I've tried to keep the column names same, but you may have to make some changes:

UPDATE EMP
SET EMP.Account_Number = UBL.[Account ],
    EMP.Bank_ID = 1,
    EMP.Branch_ID = ADM.Branch_ID   
FROM EMP_Personal EMP
JOIN UBL$ UBL ON EMP_Personal.Emp_ID = UBL.[Employee ID  ]
JOIN ADM_Branch ADM ON ADM.Branch_Code = UBL.[Branch Code  ];

SELECT [Employee ID  ],[Name ],[Account],[Branch Code  ]
FROM UBL$ 
WHERE [Branch Code  ] NOT IN (SELECT Branch_Code FROM ADM_Branch);

Upvotes: 1

Ahsen
Ahsen

Reputation: 386

Thank you all I did it in this way;

UPDATE EMP_Personal
SET account_number = s.[Account ] , Bank_ID = 1 , Branch_ID= (select Branch_ID from ADM_Branch join UBL$ on ADM_Branch.Branch_Code = UBL$.[Branch Code  ] where EMP_Personal.Emp_ID = UBL$.[Employee ID  ])
FROM emp_personal
INNER JOIN (
SELECT UBL$.[Account ] , UBL$.[Employee ID  ]
FROM UBL$
INNER JOIN adm_branch
ON adm_branch.branch_code = UBL$.[Branch Code  ] ) as s
ON s.[Employee ID  ] = emp_personal.Emp_ID

Upvotes: 1

huhu78
huhu78

Reputation: 389

For a conditional INSERT/UPDATE/DELETE it is much better to use a MERGE statement. Its syntax is not easy at first and a final statement is very long but this is very powerful tool. I recommend you to learn it. MERGE is accessible in MS SQL Server 2008 and higher releases.

Upvotes: 1

Related Questions