SQL Query to update or insert row with If Exists statement not working

previously while posting i missed out table b which hold urn and org_ref . I have three tables. Table a ,b and c, where I have to update the column "total Emp" of table c based on the URN Key in table a. Table B doesnt have URN column but it has Org_ref which so to connect the table a and table c we need to join table b on a.urn=b.urn. I have written a query to loop through the record and do the insert or update, but it's not working and doesn't update. Just insert with blank "Total employee" in table b.

table a

   |urn  |total employee
   |333  |25
   |123  |26
   |21   |2
   |11   |23

Table B

   |urn  |org_ref
   |333  |1234
   |123  |343
   |21   |2
   |11   |23

Table C

   org_ref    total employe 
   1234            0       --should update from table a 
    343            0       --should update from table a 
                           --add record from table a org_ref 21
                           --add record from table a org_ref 11

Here is my script

Create table #orgs(
iorgrowid int identity (1,1),
ORG_REF  varchar(255),
Total_Leader int,
urn int
)


insert into #orgs

select  ORG.organisation_ref,s.[TOTAL_LEADERSHIP_ROLE],s.urn

    from DiTestDatabase.dbo.organisation org

    JOIN DataConversion.dbo.School_plus_download_NI s on org.total_headcount=s.urn 

DECLARE 
@iReturnCode int,
@iNextcodeRowId int,
@icodeRowId int,
@iCurrentcodeRowId int,
@iNextattRowId int,
@iCurrentattRowId int,
@icodeLoopControl int,
@ORGREF INT,
@counter int,
@Ly_employees int,
@orgnamecount int,
@add1count int,
@urn int


SELECT @iCodeLoopControl = 1
SELECT @iNextCodeRowId = MIN(iorgrowId)
FROM #orgs


IF ISNULL(@iNextCodeRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END


SELECT
@iCurrentcodeRowId = iorgrowId,
@ORGREF=ORG_REF,
@Ly_employees=Total_Leader,
@urn=urn
FROM #orgs
WHERE iorgRowId = @iNextcodeRowId


set @counter = 0    
set @orgnamecount=0
set @add1count=0

--Start the main processing loop.
WHILE @iCodeLoopControl = 1



BEGIN


        begin
          set @counter = @counter + 1
         print 'The counter is ' + cast(@counter as char)  + cast(@ORGREF as char)
        end

IF EXISTS (SELECT OG.ORGANISATION_REF
FROM ORG_ACCOUNT OG WHERE OG.ORGANISATION_REF= @ORGREF
)

BEGIN
  UPDATE ORG_ACCOUNT
  SET LY_EMPLOYEES= (SELECT SP.TOTAL_LEADERSHIP_ROLE
            FROM DataConversion.dbo.School_plus_download_NI SP
            JOIN ORGANISATION ORG ON ORG.TOTAL_HEADCOUNT=SP.URN
            JOIN ORG_ACCOUNT OA ON OA.ORGANISATION_REF=ORG.ORGANISATION_REF
            WHERE OA.ORGANISATION_REF=@ORGREF)
            FROM DataConversion.dbo.School_plus_download_NI SP WHERE SP.URN=@urn

    END

ELSE

BEGIN

   alter table ditestdatabase.dbo.org_account disable trigger all   

    INSERT INTO ORG_ACCOUNT
    (ORGANISATION_REF,LY_TURNOVER,LY_PROFIT,LY_REMUN,LY_EMPLOYEES,PY_TURNOVER,PY_PROFIT,PY_REMUN,PY_EMPLOYEES,UK_TURNOVER,UK_PROFIT,UK_REMUN,UK_EMPLOYEES,CREATED_BY,
    CREATE_TIMESTAMP,UPDATED_BY,UPDATE_TIMESTAMP,PY_POT_SUB,LY_POT_SUB,LY_DATE,PY_DATE,UK_DATE)
    VALUES(@ORGREF,NULL,NULL,NULL,@Ly_employees,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,null,GETDATE(),null,null,null,null,null,null,null)

    set @orgref=(select organisation_ref from DiTestDatabase.dbo.ORG_ACCOUNT where ORGANISATION_REF=@ORGREF)

        end

        alter table ditestdatabase.dbo.org_account enable trigger all       







SELECT @iNextCodeRowId = NULL

--Get the next iRowId.
SELECT @iNextcodeRowId = MIN(iorgrowId)
FROM #orgs
WHERE iorgrowId > @iCurrentcodeRowId

--Did we get a valid next row id?
IF ISNULL(@iNextCodeRowId,0) = 0


BEGIN
BREAK
END

SELECT @iCurrentcodeRowId = iorgrowId,
@ORGREF=ORG_REF,
@Ly_employees=Total_Leader
FROM #orgs
WHERE iorgRowId = @iNextcodeRowId

END

DROP TABLE #orgs

RETURN

Solution :- As i dont have anything common between table a and c hence i have to take help of table b where the only common thing between a and b is column URN and the only column common between b and c is Org_ref. I have made the changes on my update as

SET LY_EMPLOYEES= (SELECT SP.TOTAL_LEADERSHIP_ROLE
            FROM DataConversion.dbo.School_plus_download_NI SP
            JOIN ORGANISATION ORG ON ORG.TOTAL_HEADCOUNT=SP.URN
            JOIN ORG_ACCOUNT OA ON OA.ORGANISATION_REF=ORG.ORGANISATION_REF
            WHERE sp.URN = @urn) --@ORGREF)

            FROM ORG_ACCOUNT WHERE ORGANISATION_REF=@ORG_REF

Which solved the issue.

Thanks all

Upvotes: 0

Answers (4)

Sahi

Reputation: 1484

is this helpful.?

    CREATE TABLE #a(urn bigint,  total_employe bigint)

    CREATE TABLE #b(urn bigint,org_ref bigint)

    CREATE TABLE #c(org_ref bigint,total_employe bigint)

    INSERT INTO #a
    SELECT 333,25 Union ALL
    SELECT 123,26 Union ALL
    SELECT 21,2 Union ALL
    SELECT 11,23




    INSERT INTO #b
    SELECT 333,1234 Union ALL
    SELECT 123,343 Union ALL
    SELECT 21,2 Union ALL
    SELECT 11,23 


    INSERT INTO #c
    SELECT 1234,0 Union ALL
    SELECT 343,0



    ;with cte
    AS
    (
    SELECT b.org_ref,a.total_employe from #a a
    INNER JOIN #b b on a.urn=b.urn
    )


    MERGE INTO #c c
    USING cte  on c.org_ref=cte.org_ref 
    WHEN MATCHED THEN
    UPDATE
    SET c.total_employe=cte.total_employe

    WHEN NOT MATCHED THEN
    INSERT(org_ref,total_employe)
    VALUES(org_ref,total_employe);

    SELECT * FROM #c


    DROP TABLE #a
    DROP TABLE #b
    DROP TABLE #c

Upvotes: 1

Lutz Kadoch

Reputation: 1

How about an SELECT INTO? Something like:

SELECT b.org_ref, a.totalemployee INTO c FROM a LEFT JOIN b ON (a.urn = b.urn)

Upvotes: 0

Uttam Neupane

Reputation: 66

--UPDATE
UPDATE B SET B.TOTAL_LEADER = A.TOTAL_LEADER FROM TABLEA A INNER JOIN TABLEB B ON 
A.ORG_REF = B.ORG_REF
--INSERT
INSERT INTO TABLEB(ORG_REF,URN,TOTAL_LEADER)
SELECT A.ORG_REF,A.URN,A.TOTAL_LEADER FROM TABLEA A LEFT OUTER JOIN  TABLEB B ON 
A.ORG_REF = B.ORG_REF WHERE B.ORG_REF IS NULL

I Think you are looking for this one.

Upvotes: 0

George Menoutis

Reputation: 7240

Do not use a while loop or a cursor for a simple upsert operation! You will find everywhere on the web that it is counterproductive!

I will answer in the simpler terms of table a/b:

-- First, update the ones that exist
update alias_b -- Note that you NEED an alias to update using join
set [total employee]=a.[total employee]
from a 
inner join b alias_b on a.org_ref=alias_b.org_ref

-- Then, insert the ones that don't
insert b
select (the columns you want)
from a
where not exists (select 1 from b where a.org_ref=b.org_ref)

Upvotes: 0

SQL Query to update or insert row with If Exists statement not working

Answers (4)

Related Questions