Check if Email Exists then update else insert record in Database in SQL Server

chetan

SOLVED CLOSED ACTIVE ANSWERED
User: chetan
Posted: on Jul 10, 2018 01:38 AM
Forum: SQL Server
Answer: 2
Views: 7341

i wanted to update the contactperson if email exists.i have wrtten the following query to update it in SP.

There are many other fields.What happeniing is that: if the contactperson name say is Alex and i wanted to update it to Alexa,it is not getting change.

 update cmp
 
 set cmp.companyname=case when cmp.companyname='' or cmp.companyname=null then dupl.companyname else cmp.companyname end ,
 cmp.website= case when cmp.website='' OR cmp.website IS NULL then dupl.website else cmp.website end ,
 
 cmp.contactperson= case when cmp.contactperson='' OR cmp.contactperson IS NULL then dupl.contactperson else cmp.contactperson end,
 
 cmp.country = case when cmp.country=1 OR cmp.country IS NULL then dupl.country else cmp.country end,
 cmp.telphone=case when cmp.telphone='' OR cmp.telphone IS NULL then dupl.telphone else cmp.telphone end,cmp.mobile= case when cmp.mobile='' OR cmp.mobile IS NULL then dupl.mobile else cmp.mobile end ,
 cmp.fax= case when cmp.fax='' OR cmp.fax IS NULL then dupl.fax else cmp.fax end, cmp.region= case when cmp.region=2 OR cmp.region IS NULL then dupl.region else cmp.region end,
 cmp.address= case when cmp.address='' OR cmp.address IS NULL then dupl.address else cmp.address end,cmp.designation= case when cmp.designation='' OR cmp.designation IS NULL then dupl.designation else cmp.designation end,
 cmp.products= case when cmp.products='' OR cmp.products IS NULL then dupl.products else cmp.products end
 from companyinfo cmp
 inner join #DuplTempTable dupl on cmp.ID=dupl.ID

hi sir...cmp is companyinfo .....email field is there in emailinfo table...

comapanyinfo: ID,Dataid,companyname

emailinfo: ID,Dataid,email

how to write it in a select query?

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Jul 10, 2018 01:47 AM
Modified: on Jul 10, 2018 01:50 AM
Report

Hi chetan,

You have to use SQL EXISTS function to check whether record exist or not based on Email. Then do your rest insert and update process.

Check the below sample query. Now please take its reference and correct your query.

SQL

CREATE PROCEDURE ProcedureName
      @Email NVARCHAR(50),
      @ContactPerson NVARCHAR(50)
AS
BEGIN
      SET NOCOUNT ON;
      IF EXISTS(SELECT ContactPerson FROM cmp WHERE Email = @Email)
      BEGIN
            --Your Update Query.
            UPDATE cmp
            SET ContactPerson = @ContactPerson
            WHERE Email = @Email
      END
      ELSE
      BEGIN
            --Your Insert Query.
            INSERT INTO cmp (ContactPerson,Email) VALUES (@ContactPerson,@Email)
      END
END

For more details refer below article.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Check if Email Exists then update else insert record in Database in SQL Server

Stored Procedure: Return True if record exists and False if record does not exist in SQL Server

Company

Explore

Follow us

Contact Us