Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert into using a where clause

Posted on 2004-10-22
7
Medium Priority
?
13,815 Views
Last Modified: 2009-12-14
Hi
Does anyone know whether this is possible.

I want to insert 2 values into a table, only if 1 of the values match the value in a table column

i.e
@Number VarChar(100),
@Code VarChar(400)
AS
insert into mytable
(NumberValue, CodeValue)
Values(@Number,
@Code)
Where NumberValue <> @Number and CodeValue = @Code

The above will not work and I do not know how to produce the code that will.

Any help would be appreciated.
Caz

0
Comment
Question by:BeginningWebDesign
[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
7 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12386225
Try putting an if on it.

declare @Number int, @Code int
select NumberValue, CodeValue from mytable where NumberValue <> @Number and CodeValue = @Code

if @@rowcount > 0
begin

insert into mytable (NumberValue, CodeValue)
Values(@Number,@Code)

end

Cheers
0
 
LVL 4

Assisted Solution

by:BulZeyE
BulZeyE earned 1000 total points
ID: 12386568
What about:

Insert into Mytable
  (NumberValue, CodeValue)
select top 1 @Number, @Code
 from MyTable
where NumberValue <> @Number
and CodeValue = @Code
0
 
LVL 8

Accepted Solution

by:
sigmacon earned 1000 total points
ID: 12386699
You need to check two things: Does the CodeValue exist? If so, you need to make sure that a row with that number for that code does NOT exists. I tried to combine the statements, but they have a counter-point: if the code does not exist, neither does the row with the number value for that code:

...
@Number VarChar(100),
@Code VarChar(400)
AS

if exists ( select top 1 * from mytable Where CodeValue = @Code )
  and not exists ( select * from mytable Where NumberValue = @Number and CodeValue = @Code )
     insert into mytable ( NumberValue, CodeValue )
     Values ( @Number, @Code )
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 10

Expert Comment

by:imrancs
ID: 12388215
OR

INSERT INTO Mytable  (NumberValue, CodeValue)
SELECT @Number, @Code
WHERE NOT EXISTS(SELECT * FROM MyTable NumberValue <> @Number and CodeValue = @Code)


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12388229
My suggestion is little modified version of one suggested by BulZeyE above.

Imran
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 12389420
Both BulZeyE and imrancs version will allow to insert rows that based on your initial statement you did not want to have inserted. If you don't care about duplicate rows in your table, you can ignore the rest of this post.

Lets say you have this in your table:

NumberValue      CodeValue
1                         'bla'
1                         'doh'
2                         'bla'
2                         'doh'

If you now try to insert a row with @number = 1 and @code = 'bla', it will insert, because there is a row that fullfills

NumberValue <> @number and CodeValue = @code

It's the row with NumberValue = 2 and CodeValue = 'bla'

For that reason, you need two checks: One for whether the CodeValue already exists in the table and one for making sure that a row with a given number for the code does not yet exist.
0
 

Author Comment

by:BeginningWebDesign
ID: 12395426
Thanks Everyone

Code works in Query but when tested on site does not work, I'm working on the problem

Caz
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

604 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