How to update column values with matching ids-CodePudding

I have following table (Users) with 2 columns and trying to update second column with first column ( first column is autoincrement and primary key field).

UserId  CatId
1         10
2         78
3         99
4         89
5         80

I am finding difficult to update second column with P_uid order by (1,3,4). I tried normal Update statement but it doesn't work. I am passing following ids into SP.

P_uid varchar2(20) := '1,3,4';
P_new_cat_id varchar2(20) := '100,12,13';

Expected output
---------------
UserId  CatId
1         100
2         78
3         12
4         13
5         80

CodePudding user response：

Use a MERGE statement:

MERGE INTO users dst
USING (
  SELECT 1 AS id, 100 AS new_catid FROM DUAL UNION ALL
  SELECT 3, 12 FROM DUAL UNION ALL
  SELECT 4, 13 FROM DUAL
) src
ON (src.id = dst.userid)
WHEN MATCHED THEN
  UPDATE
    SET catid = src.new_catid;

Which, for the sample data:

CREATE TABLE users (UserId, CatId) AS
SELECT 1, 10 FROM DUAL UNION ALL
SELECT 2, 78 FROM DUAL UNION ALL
SELECT 3, 99 FROM DUAL UNION ALL
SELECT 4, 89 FROM DUAL UNION ALL
SELECT 5, 80 FROM DUAL;

Then, after the merge, the table contains:

USERID CATID

1 100

2 78

3 12

4 13

5 80

USERID	CATID
1	100
2	78
3	12
4	13
5	80

If you have delimited strings then (apart from finding a better solution for passing multi-row data) you can split the values into rows using a row-generator function and use the previous technique:

DECLARE
  P_uid varchar2(20) := '1,3,4';
  P_new_cat_id varchar2(20) := '100,12,13';
BEGIN
  MERGE INTO users dst
  USING (
    SELECT REGEXP_SUBSTR(p_uid,        '\d ', 1, LEVEL) AS id,
           REGEXP_SUBSTR(p_new_cat_id, '\d ', 1, LEVEL) AS new_catid
    FROM   DUAL
    CONNECT BY LEVEL <= LEAST(
                          REGEXP_COUNT(p_uid,        '\d '),
                          REGEXP_COUNT(p_new_cat_id, '\d ')
                        )
  ) src
  ON (src.id = dst.userid)
  WHEN MATCHED THEN
    UPDATE
      SET catid = src.new_catid;
END;
/

Which outputs the same.

db<>fiddle here