user207902 user207902 - 4 months ago 10
SQL Question

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.

Dan Dan
Answer

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