How to update each row of a column by the results of a select?

Question

I want to update the values of a column with the values that I have acquired through a select statement. But by performing the select I have the whole set of the results, which I want each one of them to be replaced by the czi_first_name_en (kind of a foreach loop) This is what is have so far:

UPDATE citizen_info t
SET    t.czi_first_name_en=
       (
                  SELECT     per_username
                  FROM       person
                  INNER JOIN enrollment_office
                  ON         person.per_id=enrollment_office.eof_manager_id
                  INNER JOIN card_request
                  ON         enrollment_office.eof_id=card_request.crq_enroll_office_id
                  INNER JOIN citizen
                  ON         card_request.crq_citizen_id=citizen.ctz_id
                  INNER JOIN citizen_info
                  ON         citizen.ctz_id=citizen_info.czi_id
                  WHERE      person.per_dep_id=card_request.crq_enroll_office_id) AS person_username

How must I proceed?

score 1 · Accepted Answer · answered Feb 10 '15 at 08:09

You could also use MERGE statement with only an UPDATE clause -

MERGE INTO citizen_info t 
USING (SELECT person.per_username per_username, 
              citizen.ctz_id      ctz_id 
       FROM   person 
              inner join enrollment_office 
                      ON person.per_id = enrollment_office.eof_manager_id 
              inner join card_request 
                      ON enrollment_office.eof_id = 
                         card_request.crq_enroll_office_id 
              inner join citizen 
                      ON card_request.crq_citizen_id = citizen.ctz_id 
       WHERE  person.per_dep_id = card_request.crq_enroll_office_id) s 
ON(s.ctz_id = t.czi_id) 
WHEN matched THEN 
  UPDATE SET t.czi_first_name_en = s.per_username 

/

Thank you :) your suggestions for a merge worked for me, I just added a DISTINCT keyword after SELECT — masoumeh nourollahi, Feb 15 '15 at 06:37

score 0 · Answer 2 · edited May 23 '17 at 10:24

1) You just need an UPDATE with JOIN, I think (Oracle-style).

UPDATE
(

select 

per_username as NEW, 
czi_first_name_en as OLD
from person 
inner join enrollment_office on person.per_id=enrollment_office.eof_manager_id
inner join card_request on enrollment_office.eof_id=card_request.crq_enroll_office_id 
inner join citizen on card_request.crq_citizen_id=citizen.ctz_id 
inner join citizen_info on citizen.ctz_id=citizen_info.czi_id 
where person.per_dep_id=card_request.crq_enroll_office_id
) t

SET t.OLD = t.NEW

See also:

Update statement with inner join on Oracle

2) You can also do it your way, I think.

 UPDATE citizen_info t
 SET t.czi_first_name_en=
 (
 SELECT 
 per_username
 FROM person
 inner join enrollment_office ON person.per_id=enrollment_office.eof_manager_id
 inner join card_request ON enrollment_office.eof_id=card_request.crq_enroll_office_id
 inner join citizen ON card_request.crq_citizen_id=citizen.ctz_id 
 inner join citizen_info x ON citizen.ctz_id=x.czi_id
 WHERE person.per_dep_id=card_request.crq_enroll_office_id and x.czi_id=t.czi_id
 ) as person_username

Note the only difference here: x.czi_id=t.czi_id. This links the x record to the t record which you want to update.

score 0 · Answer 3 · answered Feb 10 '15 at 08:05

You have to "connect" the update row with the sub-select row, probably using some id, see my attempt at the end of the sub-select:

update Citizen_Info t
      set t.czi_first_name_en =
         (select per_username
          from person inner join enrollment_office
              on person.per_id = enrollment_office.eof_manager_id
            inner join card_request on
              enrollment_office.eof_id = card_request.crq_enroll_office_id
            inner join citizen on card_request.crq_citizen_id = citizen.ctz_id
            inner join citizen_info on citizen.ctz_id = citizen_info.czi_id
          where person.per_dep_id = card_request.crq_enroll_office_id
            **and t.perid = person.per_id**)

score 0 · Answer 4 · answered Feb 10 '15 at 08:06

0

Read about MERGE function http://en.wikipedia.org/wiki/Merge_%28SQL%29 You specify the target and source then perform specific actions like update if matching conditions are satisfied

answered Feb 10 '15 at 08:06

fuggy_yama

607
6
24

How to update each row of a column by the results of a select?

4 Answers4