CODEWITHSUNDEEP
sqlprocedure
user207902
user207902

Reputation: 85

update each row in a procedure

I have the following data in a table TABLE1

DOCUMENT ------ FIELD1
12345
23456
34567
45678
98765

i have the following data in a view VIEW1

DOCUMENT ---- BUS
12345 ------------ 5
23456 ------------ 6
34567 ------------ 8
45678 ------------ 12
98765 ------------ 14

What i would like to do is update each row

if (table1.document = view1.document)
then table1.field1 = view1.bus

Any insight will help.

Thank you.

Upvotes: 1

Views: 5928

Answers (2)

Kristen
Kristen

Reputation: 4291

As Dan said, but in MS SQL Server I find this styling easier to read:

UPDATE U
SET U.field1 = V.bus 
FROM table1 AS U
    JOIN view1 AS V
       ON V.document = U.document

Note that if VIEW1 could have multiple rows for a given TABLE1 row [DOCUMENT] value then the [BUS] value choosen to update TABLE1 will be random, within the matching set. (If this is the case the query could be modified to choose MAX / MIN / etc.)

I would refine this query to NOT update any rows that already matched the BUS value, which will make it faster if it is rerun and thus some values already exist in TABLE1

UPDATE U
SET U.field1 = V.bus 
FROM table1 AS U
    JOIN view1 AS V
       ON V.document = U.document
WHERE    U.field1 = V.bus
      OR (U.field1 IS NOT NULL AND V.bus IS NULL)
      OR (U.field1 IS NULL AND V.bus IS NOT NULL)

you can leave out the NULL / NOT NULL tests if the field is defined as not allowing NULLs.

Upvotes: 1

Dan
Dan

Reputation: 11069

That can be done using plain SQL, no procedures required:

UPDATE table1 SET field1 = (SELECT bus FROM view1 WHERE table1.document = view1.document)

Or, if your database allows it:

UPDATE (select table1.field1, view1.bus FROM table1 JOIN view1 ON table1.document = view1.document) SET table1.field1 = view1.bus

Upvotes: 2

Related Questions