October 6, 2011 at 1:15 pm
I'm tired and need your help...
Table: CUSTOMER
Columns: col_1, col_2, col_3
What I need is a statement that says IF col_1 = X, col_2 = Y and col_3 = Z in CUSTOMER, then do NOTHING/STOP/END/FINISH/CLOSE (whatever), ELSE INSERT INTO CUSTOMER VALUES...
This is what I was thinking (see below), but is that right syntax (to start, I have the insert statement to add at the end)? I literally want absolutely nothing to happen if a row has any of these details. I cannot have duplicate values here... VERY BAD.
if (exists (select count(*) from customer where col_1 = x, col_2 = y, col_3 = z group by col_1, col_2, col_3 HAVING ( COUNT(col_1) > 1, COUNT(col_2) > 1, COUNT(col_3) > 1 )))
October 6, 2011 at 1:30 pm
IF NOT EXISTS(SELECT * FROM Customer WHERE col1 = @x AND col2 = @y AND @col3 = @z)
BEGIN
-- do stuff
END
Eddie Wuerch
MCM: SQL
October 6, 2011 at 1:30 pm
SQL_Padawan_Learner (10/6/2011)
I'm tired and need your help...Table: CUSTOMER
Columns: col_1, col_2, col_3
What I need is a statement that says IF col_1 = X, col_2 = Y and col_3 = Z in CUSTOMER, then do NOTHING/STOP/END/FINISH/CLOSE (whatever), ELSE INSERT INTO CUSTOMER VALUES...
This is what I was thinking (see below), but is that right syntax (to start, I have the insert statement to add at the end)? I literally want absolutely nothing to happen if a row has any of these details. I cannot have duplicate values here... VERY BAD.
if (exists (select count(*) from customer where col_1 = x, col_2 = y, col_3 = z group by col_1, col_2, col_3 HAVING ( COUNT(col_1) > 1, COUNT(col_2) > 1, COUNT(col_3) > 1 )))
Why not use Not Exists?
if not exists(select * from customer where col_1 = x and col_2 = y and col_3 = z)
begin
--do your insert here
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:31 pm
LOL Aside from the comment looks like Eddie and I answered with the exact same thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:33 pm
Eddie Wuerch (10/6/2011)
IF NOT EXISTS(SELECT * FROM Customer WHERE col1 = @x AND col2 = @y AND @col3 = @z)BEGIN
-- do stuff
END
I may be wrong but if using NOT EXISTS wouldn't the ANDs become ORs?
October 6, 2011 at 1:35 pm
what you've posted is not a valid SELECT statement, comparisons within a WHERE clause or connected by AND or OR - not commas.
As far as doing nothing if it does exists, then the easiest approach is to use NOT EXISTS()
Is this what you meant?
if not exists (
select 1
from customer
where (col_1 = 'X' or col_2 = 'Y' or col_3 = 'Z')
)
insert ...
October 6, 2011 at 1:36 pm
Lynn Pettis (10/6/2011)
Eddie Wuerch (10/6/2011)
IF NOT EXISTS(SELECT * FROM Customer WHERE col1 = @x AND col2 = @y AND @col3 = @z)BEGIN
-- do stuff
END
I may be wrong but if using NOT EXISTS wouldn't the ANDs become ORs?
As per the OP if a record exists with those 3 columns they want to do nothing. In other words if there is a record where col1 = x and col2 = y and col3 = z this record already exists so we dont want to do anything. I do see the confusion since the OP used if Exists and the logic for the check was reversed. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 6, 2011 at 1:46 pm
Did some testing and it looks like you do want to ANDs in the NOT EXISTS statement.
Okay, boolean logic doesn't always work as you think in T-SQL.
October 6, 2011 at 1:52 pm
Actually, I think I found where my logic died. Haven't used it in a while so I didn't quite remember the rules correctly.
I stand corrected and back on track with boolean logic.
October 6, 2011 at 1:55 pm
Lynn Pettis (10/6/2011)
Did some testing and it looks like you do want to ANDs in the NOT EXISTS statement.Okay, boolean logic doesn't always work as you think in T-SQL.
Boolean logic does, but negating the entire EXISTS is different from negating the where clause. Boolean logic states:
WHERE !(A and B and C) == WHERE (!A OR !B OR !C)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2011 at 1:55 pm
If you're not concerned about portability (T-SQL doesn't require a FROM clause), you might get away with a single INSERT statement:
INSERT INTO Customer (
Col1,
Col2,
Col3
)
SELECT @X,
@Y,
@Z
WHERE NOT EXISTS (
SELECT 1
FROM Customer
WHERE Col1 = @X
AND Col2 = @Y
AND Col3 = @Z
);
--SJT
October 6, 2011 at 2:01 pm
GilaMonster (10/6/2011)
Lynn Pettis (10/6/2011)
Did some testing and it looks like you do want to ANDs in the NOT EXISTS statement.Okay, boolean logic doesn't always work as you think in T-SQL.
Boolean logic does, but negating the entire EXISTS is different from negating the where clause. Boolean logic states:
WHERE !(A and B and C) == WHERE (!A OR !B OR !C)
That's what I messed up on, and figured out again with some testing. When you don't use something for a while, things tend to get foggy.
I know, I'll blame it on Oracle, yea, that's it. :w00t:
October 6, 2011 at 4:04 pm
Thanks everyone; however, I'm still inserting dups... :angry:
I declared my variables and the message (after I execute the script) shows me the values (because I said to print the @x, @y, etc. after I set it). Could that be the problem? Could the where clause using a col1 = @y be the problem? How can I get around this?
In the meantime, I'm going to hard code the values and see if that works. This script need to be flexible to allow for variables to be used. Any hard coding is going to diminish the value of the script. thanks everyone...
October 6, 2011 at 4:07 pm
Nevermind, I'm an idiot... My variables are too alike (shame on me). I was telling it look for ABC when I really needed it look for 123.... Sorry for the confusion... As always, you guys always come through for me!!!
I owe you one...
October 6, 2011 at 4:15 pm
This is the code that I used...
IF EXISTS (SELECT *
FROM
Customers
WHERE
Col_1 = @x
AND Col_2 = @y
AND Col_3 = @z)
BEGIN
RAISERROR('Record already exists',
16,
1)
ROLLBACK TRAN
END
ELSE
IF NOT EXISTS (SELECT *
FROM
Customers
WHERE
Col_1 = @x
AND Col_2 = @y
AND Col_3 = @z)
BEGIN
INSERT INTO Customer
VALUES (Substring(Replace(Newid(), '-', ''), 1, 32)
,@x
,@y
,@z
,Getdate()
,Getdate()
,'USER_ID'
,'USER_ID')
END
When the record is there, here's the response I get.
Msg 50000, Level 16, State 1, Line 68
Record already exists
Msg 3903, Level 16, State 1, Line 72
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply