CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3614477
user3614477

Reputation: 25

SQL Server Procedure to Update Column value

hello I am new to writing procedures in SQL Server.

I am trying to write a procedure to do some thing like this

LOGIC

If ( Table1. ColA.Value = Table2. colB.Value)
then
Table1.ColC.Value  = Table1. ColA.Value

(If Col A value of Table 1 = Col B value of Table 2)

THEN Update the value of Table1.ColC as the same value of Table1.ColA


CREATE PROCEDURE dbo.stored1

AS
BEGIN

DECLARE @Var1 AS nvarchar(50)
DECLARE @Var2 AS nvarchar(50)

SELECT @Var1 = Test.dbo.Unit.HMY
FROM Test.dbo.Unit

SELECT @Var2 = Test.dbo.UnitTypeExtract.HPROPERTY
FROM Test.dbo.UnitTypeExtract

SELECT CASE WHEN @Var1 = @Var2

THEN

UPDATE Test.dbo.Unit.UnitType = @Var1

END

The procedure runs well but no rows are affected. I am sure about the data as I compared it for similarity.

Upvotes: 1

Views: 107

Answers (3)

K Richard
K Richard

Reputation: 1984

SQL is more declarative than how you are trying to use it in your question. In very general terms, you tell it what produce rather than how to produce it. Your first example before the CREATE PROCEDURE piece is easier for me to understand, and here's how I interpret it:

UPDATE Table1
    SET Table1.ColC = Table1.ColA
FROM
    Table1
    INNER JOIN
    Table2
    ON
        Table1.ColA = Table2.ColB;

I would also comment that the above usage seems a little off to me. What's causing that is I have not seen a reasonable need to update in a table to another value in the same table/same row. Review your logic carefully compared to what the business need is, because it seems off to me.

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Your below code

SELECT CASE WHEN @Var1 = @Var2

THEN

UPDATE Test.dbo.Unit.UnitType = @Var1 <-- here missing set operator

Can be simplified to this directly

UPDATE Test.dbo.Unit set UnitType = @Var1 where @Var1 = @Var2

Upvotes: 0

fnightangel
fnightangel

Reputation: 426

Change your update and your select case to:

IF @Var1 = @Var2 BEGIN

   UPDATE Test.dbo.Unit
   SET UnitType = @Var1

END

But pay attention. This way you will update all rows. Is that really what you want?

Upvotes: 0

Related Questions