How do I update every row of a particular column with values from another column?-CodePudding

I am trying to update a column called software_id with a possible three values from a lookup table. So update user softwares software id column with the values from the softwares table of the id column. But there are only three rows in the softwares table. In the user softwares table there are over 1000. But when I run the query below only three rows get updated and the rest are left as null.

So the softwares id column is number and goes 1,2,3 and the user_sofwares software_id column is all null.

When I run the following query.

UPDATE user_software c 
SET sotware_id = (
                  SELECT DISTINCT id
                  FROM softwares
                  WHERE id = c.id
                  );

Only the first three rows of the destination table get updated. I want every row on the user_softwares column to be updated with either 1,2 or 3. So it should be 1,2,3,1,2,3 for example.

I keep getting this error whenever i tried to update all rows

Error report - ORA-01427: single-row subquery returns more than one row

CodePudding user response：

You can do this with a MERGE statement:

MERGE INTO (SELECT software_id, 1, ora_hash(ROWID, 2)   1 AS fake_id
              FROM user_software) u_soft
USING (SELECT DISTINCT id    
         FROM softwares) sftw
   ON (sftw.id = u_soft.fake_id)
 WHEN MATCHED THEN UPDATE SET u_soft.software_id = sftw.id;

(considering your matches are unique)