DECODE

Prev: Different databases or different schemas?
Next: V$active_session_history.time_waited

From: Mtek on 24 Jun 2008 13:09

---

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

From: Terry Dykstra on 24 Jun 2008 13:39

---

"Mtek" <mtek(a)mtekusa.com> wrote in message
news:0e367c3d-5ab5-4bff-8ed1-274ef467041b(a)m45g2000hsb.googlegroups.com...
>
> 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

UPDATE table1
SET column1 = (select DECODE(....) from ....where ..)

--
Terry Dykstra

From: Marcin Wróblewski on 24 Jun 2008 15:41

---

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 B

 | 
Pages: 1
Prev: Different databases or different schemas?
Next: V$active_session_history.time_waited