CODEWITHSUNDEEP
sqlsql-serversql-update
Chad
Chad

Reputation: 103

How can I update various sql columns based on another column of the same row?

I am running SQL Server and am needing to update hundreds of entries. Column A has unique values and based on that I need to update column B of the same table, all with different values. Essentially I am looking for a way to perform the following but in a bulk manner:

UPDATE table
set column B = 'value'
where column A = 'unique value'

Upvotes: 3

Views: 130

Answers (5)

Chad
Chad

Reputation: 103

Thanks @Jim Macaulay. That did it. Thank you everyone else for your input.

Upvotes: 0

M.Hemant
M.Hemant

Reputation: 2355

Here, you need to write cursor to update a column value in the same table

BEGIN 

    DECLARE c_a, c_b TEXT;

    DECLARE c_ab CURSOR FOR

    SELECT column_a,column_b FROM c_table;


   OPEN c_ab;

   LOOP 

   FETCH c_ab into c_ca, c_cb;

       IF c_ca = "0" THEN
        update c_table SET column_b = "No" WHERE 
        column_a = c_ca;
       END IF;

       IF c_ca = "1" THEN
        update c_table SET column_b = "Yes" WHERE 
        column_a = c_ca;
       END IF;

   END LOOP; 

   CLOSE c_ab; 
END

Working and tested code [please refer some cursor tutorials to update according to your condition] and with this you can update table in bulk and speedy

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

Is this what you are expecting,

UPDATE table
set column B = case when column A = 5 then 'unique string' end;

Upvotes: 2

Tony Thomas
Tony Thomas

Reputation: 436

With hundreds of values you better have the {unique value - update value} mapping defined in a separate table as TT is asking. But otherwise, if you are okay with hard-coding those mappings in your UPDATE statement:

UPDATE t
SET B = CASE A
        WHEN 'unique value1' THEN 'value1'
        WHEN 'unique value2' THEN 'value2'
        WHEN 'unique value3' THEN 'value3'
        ELSE 'Unknown'
    END
FROM table t

BTW, the above is only for 3 mappings

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

How about:

update table set
columnB = concat('unique ', columnA)

you may, or may not, need a where clause, depending on your needs.

Upvotes: 0

Related Questions