1

Firstly, I'm not used to SQL syntax at all. I face the following issue. T1 and C2 are my two tables. I'd look to do this: 

UPDATE T1 t 
SET t.adr = C2.adr 
WHERE t.cli != C2.cli;

I have the following errors : C2.adr and C2.cli : invalid identifier

Am I supposed to do this : 

UPDATE T1 t, C2 c 
SET t.adr = c.adr 
WHERE t.cli != c.cli;

It's just a bit weird, because I don't update C2, but maybe, it's just the syntax, where the tables are referenced after the action (here update).

Ingo Karkat
  • 167,457
  • 16
  • 250
  • 324
Scipion
  • 11,449
  • 19
  • 74
  • 139
  • this might help http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join – Wombelite Nov 29 '13 at 11:18
  • @FaizanKhan: the second query is not "working fine". It's invalid SQL for Oracle. –  Nov 29 '13 at 11:24
  • What exactly do you want to achieve? As it looks at the moment you want to set the `t.adr` to the value of `c2.adr` where `t.cli is not equal to c2.cli` which may result in more than one value – Armunin Nov 29 '13 at 11:40

2 Answers2

0

This will do

UPDATE T1 t, C2 c 
SET t.adr = c.adr 
WHERE t.cli != c.cli;
Piyush
  • 120
  • 1
  • 9
0

It would be helpful if you expanded on what you were trying to achieve as the query you've posted is rather abstract and there looks like there ought to be another column which can be used to connect rows in T1 and C1.

However, from what you have asked, you can try something like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE T1 ( cli, adr ) AS
          SELECT 1, 1 FROM DUAL
UNION ALL SELECT 2, 2 FROM DUAL
UNION ALL SELECT 3, 3 FROM DUAL
UNION ALL SELECT 4, 4 FROM DUAL;

CREATE TABLE C1 ( cli, adr ) AS
          SELECT 1, 5 FROM DUAL
UNION ALL SELECT 2, 6 FROM DUAL
UNION ALL SELECT 3, 7 FROM DUAL
UNION ALL SELECT 4, 8 FROM DUAL;

Query 1:

UPDATE T1 t
SET    adr = ( SELECT MIN( c.adr )
               FROM   C1 c
               WHERE  c.cli <> t.cli )

Query 2:

SELECT * FROM T1

Results:

| CLI | ADR |
|-----|-----|
|   1 |   6 |
|   2 |   5 |
|   3 |   5 |
|   4 |   5 |

Its not quite like you asked for as I've had to put MIN( c.adr ) in to ensure the sub-query returns a single row but it gives you an example of how to reference a second table in an update query.

If you don't aggregate using MIN() then (for the test data I've created) the not equals condition will match multiple rows and you get an error:

Query 3:

UPDATE T1 t
SET    adr = ( SELECT c.adr
               FROM   C1 c
               WHERE  c.cli <> t.cli )

Results:

ORA-01427: single-row subquery returns more than one row : UPDATE T1 t SET adr = ( SELECT c.adr FROM C1 c WHERE c.cli <> t.cli )
MT0
  • 143,790
  • 11
  • 59
  • 117