Re: DECODE
Date: Tue, 24 Jun 2008 21:41:17 +0200
Message-ID: <g3rij4$fth$1@inews.gazeta.pl>
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;
9 END;
10 /
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 BReceived on Tue Jun 24 2008 - 14:41:17 CDT