CODEWITHSUNDEEP
plsqlconcatenationstring-concatenation
FunTomas
FunTomas

Reputation: 13

PL/SQL concatenate column with variable in UPDATE statement

I want to update existing user names to following format user+(value of loop iterator) . Any advice? The current statement shows the value 'app2nd' after execution

UPDATE users
SET user_name = 'user'|| v_count                
WHERE  id = c_id;

Upvotes: 1

Views: 745

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Certainly, you should provide some more information. In the meantime, as I have some time to spare, two options for you: the first one doesn't require any loop (you mentioned) but utilizes the ROWNUM:

SQL> select * from test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update test set
  2    dname = 'user' || rownum       --> this
  3    where deptno >= 20;

3 rows updated.

SQL> select * From test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 user1          DALLAS
        30 user2          CHICAGO
        40 user3          BOSTON

SQL>
SQL> rollback;

Rollback complete.

SQL>

Another option, a LOOP I invented as you didn't explain which kind of a loop you have:

SQL> begin
  2    for cur_r in (select deptno, dname,
  3                    row_number() over (order by deptno) rn
  4                    from dept)
  5    loop
  6      update test set
  7        dname = 'user' || cur_r.rn
  8        where deptno = cur_r.deptno;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select * From test;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 user1          NEW YORK
        20 user2          DALLAS
        30 user3          CHICAGO
        40 user4          BOSTON

SQL>

If it helps, fine. If not, you know what to do - express yourself in a poetic way.

Upvotes: 1

Related Questions