?
Solved

SQL check if ID exists and process different insert statement

Posted on 2014-10-13
4
Medium Priority
?
219 Views
Last Modified: 2014-10-15
The below statement should determine which part is called dependent on if the ProductID exists or not,
so if the ProductID does not exists i want query 1 to call, otherwise if it does exist i want query 2
to call, my issue lies within the SELECT statement at the top, what am I doing wrong

IF NOT EXISTS(SELECT TOP 1 ProductID FROM ProductTable)
BEGIN
--QUERY 1
INSERT
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
ProductView.UnitPrice
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
AND NOT EXISTS(SELECT 1 FROM ProductTable WHERE ProductView.ProductID = ProductTable.ProductID)
END
ELSE
--QUERY 2
INSERT
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
UnitPrice = 0.00
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
0
Comment
Question by:wint100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 40377446
Don't you need a where clause on that select?  This will never fire Query 1 if there are any items at all in ProductTable.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40377504
You must provide a table name to INSERT to:

--QUERY 1
INSERT <table_name_must_go_here>
(ProductName, UnitPrice)
0
 
LVL 1

Author Comment

by:wint100
ID: 40379492
Thanks for you help, my modified code is as follows, but an error still exists. The If statement just doesn't get called:

IF NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable)
BEGIN
--QUERY 1
INSERT INTO ProductTable
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
ProductView.UnitPrice
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)
AND NOT EXISTS(SELECT 1 FROM ProductTable WHERE ProductView.ProductID = ProductTable.ProductID)
END
ELSE
--QUERY 2
INSERT INTO ProductTable
(ProductName, UnitPrice)
SELECT
ProductView.ProductName,
UnitPrice = 0.00
FROM ProductView
INNER JOIN ProductTable ON ProductView.ProductID = ProductTable. ProductID
WHERE ProductID = (SELECT TOP 1 ProductID FROM ProductTable ORDER BY changedate DESC)

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40381434
try this as a test:

select case when NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable) then 'not exists' else 'exists' end

Query 1 cannot be executed unless you have deleted all records from [ProductTable]

Please reconsider lludden's point: You need a where clause in the very first part of our query

IF NOT EXISTS(SELECT TOP 1 PartReference FROM ProductTable WHERE SOME CONDITION IS HERE)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question