StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 <code>T1</code> and <code>C1</code>.</p> <p>However, from what you have asked, you can try something like this:</p> <p><a href="http://sqlfiddle.com/#!4/b1234/3" rel="nofollow">SQL Fiddle</a></p> <p><strong>Oracle 11g R2 Schema Setup</strong>:</p> <pre><code>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; </code></pre> <p><strong>Query 1</strong>:</p> <pre><code>UPDATE T1 t SET adr = ( SELECT MIN( c.adr ) FROM C1 c WHERE c.cli <> t.cli ) </code></pre> <p><strong>Query 2</strong>:</p> <pre><code>SELECT * FROM T1 </code></pre> <p><strong><a href="http://sqlfiddle.com/#!4/b1234/3/1" rel="nofollow">Results</a></strong>:</p> <pre><code>| CLI | ADR | |-----|-----| | 1 | 6 | | 2 | 5 | | 3 | 5 | | 4 | 5 | </code></pre> <p>Its not quite like you asked for as I've had to put <code>MIN( c.adr )</code> 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.</p> <p>If you don't aggregate using <code>MIN()</code> then (for the test data I've created) the not equals condition will match multiple rows and you get an error:</p> <p><strong>Query 3</strong>:</p> <pre><code>UPDATE T1 t SET adr = ( SELECT c.adr FROM C1 c WHERE c.cli <> t.cli ) </code></pre> <p><strong>Results</strong>:</p> <pre><code>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 ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload