用另一列中的值更新列 [英] Update column with values from another column

问题描述

我有一个这样的表:

create table foo ( a number, b number ) 

我想使用另一个表中的值更新的所有列

I want to update all the columns from a with the value that is in another table

create table bar ( x number, y number ) 

因此，如果这将是一种程序编程语言，我将:

So, if this would be a procedural programing language I would:

 foreach foo_item in foo 
     foreach bar_item in bar 
         if( foo_item.b == bar_item.y ) 
             foo_item.a = bar_item.x 
         end
     end
 end

我尝试过

update foo 
set a = ( select distinct( x ) from bar where bar.y = foo.b ) 

但是它挂起了....我不确定如何做这样的事情(甚至不是Google搜索的内容)

But it hangs.... I'm not really sure how to do such a thing ( or even what to google for )

谢谢

编辑对不起，我很抱歉.它不会挂起，但是会尝试设置va null值，并且我有一个约束(无法删除)

EDIT Sorry my bad. It doesn't hang, but it tries to set va null value and I have a constraint ( which I can't remove )

感谢您的帮助

推荐答案

update foo set b =(选择distinct( x)来自bar，其中bar.y = foo.b)

update foo set b = ( select distinct( x ) from bar where bar.y = foo.b )

出于性能原因可能会挂起，但应该可以工作.如果没有bar.y等于foo.b，请仔细检查会发生什么.如果将b设置为null是否可以?

May hang for performance reasons but should work. Double check what happends if there is no bar.y equal to foo.b. If it sets b to null is OK?

这篇关于用另一列中的值更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！