Updating joint query in Oracle

I’m trying to update the name on the trade table according to the user table however as we have so many trades I don’t want my query to update each and every trade. only the ones that have the condition below:

UPDATE TRADES TD
SET NAME =
  (SELECT AG.NAME
   FROM USERS AG
   INNER JOIN TRADES TD
  ON TD.NAME NOT IN ('None', 'Not active')
  AND AG.NAME <> TD.NAME
  AND AG.UID = TD.TID);

The above update query updates all the trades with the condition above. But if I check the select query it only returns 1000 count how can I fix this query to update only the 1000 records required and not all names in the trade table?

Answer

I think you want a where clause:

update trades td
set name = (select ag.name from users ag where ag.uid = td.tid)
where 
    td.name not in ('none', 'not active')
    and td.name <> (select ag.name from users ag where ag.uid = td.tid)

Advertisement

Answer