BeginningWebDesign
asked on
Insert into using a where clause
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OR
INSERT INTO Mytable (NumberValue, CodeValue)
SELECT @Number, @Code
WHERE NOT EXISTS(SELECT * FROM MyTable NumberValue <> @Number and CodeValue = @Code)
Imran
INSERT INTO Mytable (NumberValue, CodeValue)
SELECT @Number, @Code
WHERE NOT EXISTS(SELECT * FROM MyTable NumberValue <> @Number and CodeValue = @Code)
Imran
My suggestion is little modified version of one suggested by BulZeyE above.
Imran
Imran
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.
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.
ASKER
Thanks Everyone
Code works in Query but when tested on site does not work, I'm working on the problem
Caz
Code works in Query but when tested on site does not work, I'm working on the problem
Caz
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