update from another table in sql-CodePudding

I have two tables. I need to bring data from one table to another based on where clause.

UPDATE cats_new c
SET c.INFO_REQ = (select min(w.tRANSITION_DATE) from CATS_new_history w)
where  w.CANDIDATEID = c.CANDIDATE_ID

CodePudding user response：

This is close, but you have to move your WHERE condition into the subquery so that the subquery is "correlated":

UPDATE cats_new c
SET c.INFO_REQ = 
  (
     SELECT min(w.tRANSITION_DATE) 
     FROM CATS_new_history w
     WHERE w.CANDIDATEID = c.CANDIDATE_ID
  )

You can see more examples here. Specifically the first example labelled "Example - Update table with data from another table"

CodePudding user response：

Two options I can think of; one follows JNevill's suggestion with another piece of advice: include where clause to restrict rows which will be updated, because - if there's no match in subquery - you'll update those rows' values to NULL. That's what EXISTS part of query handles:

update cats_new c set
  c.info_req = (select min(w.transition_date)
                from cats_new_history w
                where w.candidateid = c.candidate_id
               )
  where exists (select null
                from cats_new_history a
                where a.candidateid = c.candidate_id
               );

Another option is merge; although it is usually used to "replace" upsert operation (update AND insert), no problem if you use it only for one of those - update in this case; its on clause handles which rows will be affected:

merge into cats_new c
  using (select w.candidateid, 
                min(w.transition_date) transition_date
         from cats_new_history w
         group by w.candidateid
        ) x
  on (c.candidate_id = x.candidateid)
when matched then update set
  c.info_req = x.transition_date;