Re: DECODE

Mtek pisze:
> Hi,
>
> I'm looking around for an example of this. Can you update a table
> with a DECODE statement which gets values from another table?
>
> So, something like this:
>
> UPDATE table1
> SET column1 = DECODE(some value of a select statement.......
>
> So, I want to update the column with one of two values depending on
> the result of a query against another table....
>
> Yes?
>
> Thanks,
>
> John

Sure,

SQL> CREATE TABLE t1

   2 (
   3 id INT PRIMARY KEY,
   4 V VARCHAR2(30) CHECK(v IN ('A','B'))    5 )
   6 /

Table created.

SQL> CREATE TABLE t2

   2 (
   3 id INT PRIMARY KEY,
   4 t1_id INT REFERENCES t1(id),
   5 V VARCHAR2(30))
   6 /

Table created.

SQL> BEGIN

   2  	INSERT INTO t1 VALUES (1, 'A');
   3  	INSERT INTO t1 VALUES (2, 'B');
   4  	INSERT INTO t2 VALUES (1, 1, '');
   5  	INSERT INTO t2 VALUES (2, 2, '');
   6  	INSERT INTO t2 VALUES (3, 1, '');
   7  	INSERT INTO t2 VALUES (4, 2, '');
   8  	COMMIT;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM t2 ORDER BY ID;

         ID T1_ID V

---------- ---------- ------------------------------
          1          1
          2          2
          3          1
          4          2

SQL>
SQL> UPDATE t2

   2  	SET v = DECODE( (SELECT v FROM t1 WHERE t1.id = t2.t1_id)
   3  		      , 'A', 'VALUE A'
   4  		      , 'B', 'VALUE B'
   5  		      );

4 rows updated.

SQL>
SQL> SELECT * FROM t2;

         ID T1_ID V

---------- ---------- ------------------------------
          1          1 VALUE A
          2          2 VALUE B
          3          1 VALUE A
          4          2 VALUE B

SQL> ROLLBACK; Rollback complete.

However something like this also works:

SQL> UPDATE (

   2  	SELECT t2.v
   3  	     , DECODE( t1.v
   4  		     , 'A', 'VALUE A'
   5  		     , 'B', 'VALUE B'
   6  		     ) decoded_t1_v
   7  	  FROM t1, t2
   8  	 WHERE t2.t1_id = t1.id
   9  	    )
  10  	 SET v = decoded_t1_v;

4 rows updated.

SQL> SELECT * FROM t2 ORDER BY ID;

         ID T1_ID V

---------- ---------- ------------------------------
          1          1 VALUE A
          2          2 VALUE B
          3          1 VALUE A
          4          2 VALUE B