Member Avatar for dips_1982 
I have table of three column like
Identifier  Description Value
    P1  x        20
    P2  y        20
    P3  x        20
    P4  y        20
    P5  x        20
    P6  y        20
    P7  x        20
    P8  y       20
    P9  x       20
          y     20
        x         20
        y       20
        x         20
        y        20
        x        20
        y        20
        x        20
   P1000    y                20


   I want to update value column with different values at once. I have created a stored procedure for that. But i am unable to furnish a small script for that. I have written thousands of UPDATA command to updat this list.

   Can any one help me out from this problem?
  • 4 Contributors
  • 3 Replies
  • 198 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JorgeM

Dani AI

Generated

A few practical, set-based options so you do not have to hand-write thousands of UPDATEs.

As describes, the goal is to assign different values per Identifier. As pointed out, a single UPDATE works only when every row gets the same value; and were right to ask for the criteria. For different values the general pattern is: build a mapping (Identifier -> NewValue) and do one set-based update that joins your table to that mapping.

Here are two common, robust approaches.

WITH Map(Identifier, NewValue) AS (
  VALUES
    ('P1', 100),
    ('P2', 200),
    ('P3', 300)
)
UPDATE t
SET Value = m.NewValue
FROM dbo.YourTable t
JOIN Map m ON t.Identifier = m.Identifier;

Or load the mapping from a CSV / application into a temp/staging table and use a single UPDATE ... JOIN:

CREATE TABLE #Map (Identifier VARCHAR(50), NewValue INT);
-- populate #Map from your source (INSERT, BULK INSERT, BCP, SSIS, etc.)
UPDATE t
SET Value = m.NewValue
FROM dbo.YourTable t
JOIN #Map m ON t.Identifier = m.Identifier;

Notes and quick troubleshooting tips:

  • For thousands of rows prefer the staging-table approach; it is maintainable and repeatable.
  • If you must update very large numbers of rows, update in batches to limit log growth and locking (UPDATE TOP (N) inside a loop).
  • Test with a SELECT join first to verify mappings. Use transactions and the OUTPUT clause to capture changed rows.
  • If calling from an application, a table-valued parameter or JSON (OPENJSON) can pass mappings efficiently into a stored procedure.

These approaches are set-based, fast, and safe compared with issuing thousands of individual UPDATE statements.

Recommended Answers

All 3 Replies

Member Avatar for adam_k

It all depends on the values you want to assign and the criteria used. Can you please be more specific?

Member Avatar for pritaeas

A simple UPDATE query could suffice. It all depends on what you want, for example:

UPDATE [table] SET [value] = 100 WHERE [value] = 20
Member Avatar for JorgeM

Are you planning on updating various records with the same value or different values...please be more specific.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.