We help IT Professionals succeed at work.
Get Started

SQL Query - IF Exists update else insert

887 Views
Last Modified: 2021-04-21
I need help with a query that I wrote.  Basically, it is to check and see if the item already exists.  If it does, then just do an update and if it doesn't then insert.

I got it to work partially.  It would update, but then would not insert.  Can someone take a look at my query and see if they can determine what I did wrong?

If I just run the insert section, I can insert it in and then it'll be fine.  But I want to go through a batch of items and update/insert depending.
DECLARE @SPOUTPUT TABLE(
SPEC_ID INT,
DESCRIPTION VARCHAR(MAX),
V_ID VARCHAR(30),
LENGTH INT
);

INSERT INTO @SPOUTPUT
SELECT	Z.ID, Z.DESCRIPTION, P.ID, LEN(Z.DESCRIPTION) AS LENGTH 
FROM	TEMPDB.DBO.ZZZ_SPEC Z INNER JOIN PART P 
		ON Z.ID = P.USER_1
WHERE	ISNUMERIC(USER_1) = 1 AND P.USER_1 < 20
GROUP BY Z.ID, Z.DESCRIPTION, P.ID;


IF	EXISTS (SELECT PB.PART_ID FROM PART_BINARY PB 
			WHERE PB.PART_ID <> '' AND PB.PART_ID IN (SELECT P.ID FROM PART P WHERE P.USER_1 <> '' AND ISNUMERIC(P.USER_1) = 1 AND P.USER_1 < 20))
BEGIN
	UPDATE	PART_BINARY
	SET		BITS = X.DESCRIPTION, 
			BITS_LENGTH = X.LENGTH
	FROM	PART_BINARY INNER JOIN @SPOUTPUT X
			ON PART_ID = X.V_ID;
END

ELSE

BEGIN
	INSERT INTO PART_BINARY(PART_ID, TYPE, BITS, BITS_LENGTH)
	SELECT	P.ID, 'D', Z.DESCRIPTION, LEN(Z.DESCRIPTION) AS BITS_LENGTH 
	FROM	MIS.DBO.ZZZ_SPEC Z INNER JOIN PART P 
			ON Z.ID = P.USER_1 
	WHERE	ISNUMERIC(P.USER_1) = 1 AND P.USER_1 < 20
			AND P.ID NOT IN (SELECT PART_ID FROM PART_BINARY)
	GROUP BY Z.ID, Z.DESCRIPTION, P.ID;
END

Open in new window

Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 3 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE