CODEWITHSUNDEEP
sqlsql-serversql-update
Christoph Bethge
Christoph Bethge

Reputation: 217

SQL Update with multiple conditions

What i need to do is to update 2 fields, Value and RegisteryItemID in a row to 1802.

The fields should only change if the fields Value and RegisteryItemID both contain the value 1790, and in addition another field called PhaseID has to have the value 3018 in it.

This is the Query i have written so far:

UPDATE [dbo].[ImproofPhaseItems]
SET Value = '1802',
    RegistryItemID = '1802'
WHERE Value = '1790' and
      RegistryItemID = '1790'and
      PhaseID = '3018';
GO

Is there anyway to change this to a more efficient way so that I don't have to physically type it in like i do know?

Upvotes: 0

Views: 11792

Answers (3)

Pawel Czapski
Pawel Czapski

Reputation: 1864

If you do not want to use stored procedure as The Bojan suggests, you can use below, but it is much more work as you need to provide new variables values after each update. I would suggest to use procedure, then even drop procedure if all updates have been applied:

DECLARE @NewValue nvarchar(10) 
DECLARE @PhaseID nvarchar(10) 
DECLARE @OldValue nvarchar(10) 

SET @NewValue = '1802'
SET @PhaseID = '3018'
SET @OldValue = '1790'

UPDATE [dbo].[ImproofPhaseItems]
SET Value = @NewValue,
    RegistryItemID = @NewValue
WHERE Value = @OldValue and
      RegistryItemID = @OldValue and
      PhaseID = @PhaseID;
GO


SET @NewValue = 'another new value'
SET @PhaseID = 'another Phase'
SET @OldValue = 'another old value'

UPDATE [dbo].[ImproofPhaseItems]
SET Value = @NewValue,
    RegistryItemID = @NewValue
WHERE Value = @OldValue and
      RegistryItemID = @OldValue and
      PhaseID = @PhaseID;
GO

-- and so on

Upvotes: 0

TehBoyan
TehBoyan

Reputation: 6890

Your question is a little bit vague, but, If I understand the question correctly what you are trying to do is create a stored procedure so that you don't have to type in/hard-code values each time you want to use this code.

In the link above everything is explained in detail. Just shortly. The way you create it is something like:

create procedure Something.YournameOfProcedure 
        @value1 nvarchar(50), 
        @value2 nvarchar(50) 
        @value3 nvarchar(50)  
as

update [dbo].[ImproofPhaseItems] set Value = @value1,
      RegistryItemID = @value1 where Value = @value2 and
      RegistryItemID = @value2 and
      PhaseID = '@value3;

The way you run it:

execute Something.YournameOfProcedure  N'1801', N'1302', '1321';

Then depending on what the reason to call that particular query is you can call it from your .NET code. If that is not the case and if for example the values are something as an export from another place, you can user csv files, or xml, then parse that from your sql code directly.

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

If this is a repetitive task, you want something in place so that you only have to type the sql once. One option is Microsoft Access. Create a database with a linked table to your Sql Server database. Then build a select query that exposes the fields you want to update and prompts you for the values you must provide.

If you don't have Access, you can write a web app or console application that prompts you for the values and then runs the query. This could be done in .net, php, coldfusion, java and probably other languages as well.

Upvotes: -1

Related Questions