blocks|key|3159604|text|这称为关联更新。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3159605|UPDATE+table1+t1
+++SET+(name,+desc)+=+(SELECT+t2.name,+t2.desc
+++++++++++++++++++++++++FROM+table2+t2
++++++++++++++++++++++++WHERE+t1.id+=+t2.id)
+WHERE+EXISTS+(
++++SELECT+1
++++++FROM+table2+t2
+++++WHERE+t1.id+=+t2.id+)|code-block|syntax|javascript|3159606|假设连接的结果是一个保留键的视图，您还可以|3159607|UPDATE+(SELECT+t1.id,+
+++++++++++++++t1.name+name1,
+++++++++++++++t1.desc+desc1,
+++++++++++++++t2.name+name2,
+++++++++++++++t2.desc+desc2
++++++++++FROM+table1+t1,
+++++++++++++++table2+t2
+++++++++WHERE+t1.id+=+t2.id)
+++SET+name1+=+name2,
+++++++desc1+=+desc2|3159608|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This is called a correlated update

<pre><code>UPDATE table1 t1
 SET (name, desc) = (SELECT t2.name, t2.desc
 FROM table2 t2
 WHERE t1.id = t2.id)
 WHERE EXISTS (
 SELECT 1
 FROM table2 t2
 WHERE t1.id = t2.id )
</code></pre>

Assuming the join results in a key-preserved view, you could also

<pre><code>UPDATE (SELECT t1.id, 
 t1.name name1,
 t1.desc desc1,
 t2.name name2,
 t2.desc desc2
 FROM table1 t1,
 table2 t2
 WHERE t1.id = t2.id)
 SET name1 = name2,
 desc1 = desc2
</code></pre>

blocks|key|3159660|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3159661|MERGE+INTO+table1+t1
USING
(
--+For+more+complicated+queries+you+can+use+WITH+clause+here
SELECT+*+FROM+table2
)t2
ON(t1.id+=+t2.id)
WHEN+MATCHED+THEN+UPDATE+SET
t1.name+=+t2.name,
t1.desc+=+t2.desc;|code-block|syntax|javascript|3159662|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;
</code></pre>

blocks|key|260024|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260025|UPDATE+Table1+T1+SET
T1.name+=+(SELECT+T2.name+FROM+Table2+T2+WHERE+T2.id+=+T1.id),
T1.desc+=+(SELECT+T2.desc+FROM+Table2+T2+WHERE+T2.id+=+T1.id)
WHERE+T1.id+IN+(SELECT+T2.id+FROM+Table2+T2+WHERE+T2.id+=+T1.id);|code-block|syntax|javascript|260026|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try

<pre><code>UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);
</code></pre>

blocks|key|3159642|text|Update+table+set+column+=+(select...)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3159643|对我来说从来没有用过，因为set只需要1个值-+SQL错误:+ORA-01427:单行子查询返回多行。|unstyled|3159644|以下是解决方案：|3159645|BEGIN
For+i+in+(select+id,+name,+desc+from+table1)+
LOOP
Update+table2+set+name+=+i.name,+desc+=+i.desc+where+id+=+i.id;
END+LOOP;
END;|3159646|这正是您在SQLDeveloper+worksheet上运行它的方式。他们说它很慢，但这是我在这个案例中唯一有效的解决方案。|3159647|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>Update table set column = (select...)
</code></pre>

never worked for me since set only expects 1 value - SQL Error: ORA-01427: single-row subquery returns more than one row.

here's the solution:

<pre><code>BEGIN
For i in (select id, name, desc from table1) 
LOOP
Update table2 set name = i.name, desc = i.desc where id = i.id;
END LOOP;
END;
</code></pre>

That's how exactly you run it on SQLDeveloper worksheet. They say it's slow but that's the only solution that worked for me on this case.

blocks|key|258162|text|这里似乎有一个更好的答案，它使用'in‘子句，它允许连接的多个键|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|258163|update+fp_active+set+STATE='E',+
+++LAST_DATE_MAJ+=+sysdate+where+(client,code)+in+(select+(client,code)+from+fp_detail
++where+valid+=+1)+...|code-block|syntax|javascript|258164|完整的例子在这里：http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html+-来自网络档案，因为链接是死的。|258165|问题在于，您希望在where子句中的括号中将列用作'+in‘之前的关键字，并将具有相同列名的select语句放在括号中。where+(column1，column2)+in+(+select+(column1，column2)+from+table+where+"the+set+I+want“)；|258166|entityMap|0|LINK|mutability|MUTABLE|url|https://web.archive.org/web/20130127142113/http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html^0|T|3|0|0|0|37|9|2H|0|0|0|45|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]]|E|$]]|$1|M|3|N|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

Here seems to be an even better answer with 'in' clause that allows for multiple keys for the join:
<pre><code>update fp_active set STATE='E', 
 LAST_DATE_MAJ = sysdate where (client,code) in (select (client,code) from fp_detail
 where valid = 1) ...
</code></pre>
The full example is here:
<a href="https://web.archive.org/web/20130127142113/http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html" rel="nofollow noreferrer">http://forums.devshed.com/oracle-development-96/how-to-update-from-two-tables-195893.html</a> - from web archive since link was dead.
The beef is in having the columns that you want to use as the key in parentheses in the where clause before 'in' and have the select statement with the same column names in parentheses.
where (column1,column2) in ( select (column1,column2) from table where &quot;the set I want&quot; );

blocks|key|258184|text|BEGIN
For+i+in+(select+id,+name,+desc+from+table2)+
LOOP
Update+table1+set+name+=+i.name,+desc+=+i.desc+where+id+=+i.id+and+(name+is+null+or+desc+is+null);
END+LOOP;
END;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|258185|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>BEGIN
For i in (select id, name, desc from table2) 
LOOP
Update table1 set name = i.name, desc = i.desc where id = i.id and (name is null or desc is null);
END LOOP;
END;
</code></pre>

blocks|key|260031|text|如果您表t1和它的backup+t2有很多列，这里有一种简洁的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|260032|此外，我的相关问题是，只有一些列被修改，许多行没有对这些列进行编辑，所以我希望不对这些列进行编辑-基本上是从整个表的备份中恢复列的子集。如果只想还原所有行，请跳过where子句。|260033|当然，更简单的方法是delete和insert+as+select，但在我的例子中，我需要一个只包含更新的解决方案。|260034|诀窍是，当你从一对具有重复列名的表中选择*时，第二个表将被命名为_1。|260035|++update+(
++++select+*+from+t1+join+t2+on+t2.id+=+t1.id
++++where+id+in+(
++++++select+id+from+(
++++++++select+id,+col1,+col2,+...+from+t2
++++++++minus+select+id,+col1,+col2,+...+from+t1
++++++)
++++)
++)+set+col1=col1_1,+col2=col2_1,+...|code-block|syntax|javascript|260036|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If your table t1 and it's backup t2 have many columns, here's a compact way to do it.

In addition, my related problem was that only some of the columns were modified and many rows had no edits to these columns, so I wanted to leave those alone - basically restore a subset of columns from a backup of the entire table. If you want to just restore all rows, skip the where clause.

Of course the simpler way would be to delete and insert as select, but in my case I needed a solution with just updates.

The trick is that when you do select * from a pair of tables with duplicate column names, the 2nd one will get named _1. So here's what I came up with:

<pre><code> update (
 select * from t1 join t2 on t2.id = t1.id
 where id in (
 select id from (
 select id, col1, col2, ... from t2
 minus select id, col1, col2, ... from t1
 )
 )
 ) set col1=col1_1, col2=col2_1, ...
</code></pre>

Table 1:

<pre><code>id name desc
-----------------------
1 a abc
2 b def
3 c adf
</code></pre>

Table 2:

<pre><code>id name desc
-----------------------
1 x 123
2 y 345
</code></pre>

In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's <code>name</code> and <code>desc</code> using the same <code>id</code>? So the end result I would get is

Table 1:

<pre><code>id name desc
-----------------------
1 x 123
2 y 345
3 c adf
</code></pre>

Question is taken from <a href="https://stackoverflow.com/questions/5036918/sql-update-query-with-data-from-another-table">update one table with data from another</a>, but specifically for oracle SQL.

Oracle SQL: Update a table with data from another table

表1：id    name    desc-----------------------1     a       abc2     b       def3     c       adf表2：id    name    desc-----------------------1     x       1232     y       345在oracle SQL中，如何运行sql update

问Oracle SQL:使用另一个表中的数据更新表
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle SQL:使用另一个表中的数据更新表EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle SQL:使用另一个表中的数据更新表
EN