blocks|key|2017620|text|您可以构建并执行动态sql来实现这一点，但它确实不太理想|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2017621|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

you can build and execute dynamic sql to do this, but its really not ideal

blocks|key|2017665|text|请尝试以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2017666|Update+A+a,+B+b,+SET+a.column1=b.column1+where+b.id=1|code-block|syntax|javascript|2017667|已编辑：-更新多个列|2017668|Update+A+a,+B+b,+SET+a.column1=b.column1,+a.column2=b.column2+where+b.id=1|2017669|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|$]]

Try Following

<pre><code>Update A a, B b, SET a.column1=b.column1 where b.id=1
</code></pre>

EDITED:- Update more than one column

<pre><code>Update A a, B b, SET a.column1=b.column1, a.column2=b.column2 where b.id=1
</code></pre>

blocks|key|2017739|text|不一定如您所愿，但也许使用postgres继承可能会有所帮助？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2017740|CREATE+TABLE+A+(
++++ID++++++++++++int,
++++column1+++++++text,
++++column2+++++++text,
++++column3+++++++text
);

CREATE+TABLE+B+(
++++column4+++++++text
)+INHERITS+(A);|code-block|syntax|javascript|2017741|这就避免了更新B的需要。|2017742|但一定要阅读所有的details。|offset|length|2017743|否则，您所请求的内容就不被认为是一种好的实践--不鼓励使用SELECT+*+...的视图之类的动态内容(因为这种轻微的便利可能会破坏更多的东西，而不是帮助事情)，并且您所请求的内容对于UPDATE+...+SET命令来说是等效的。|style|CODE|2017744|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.4/static/ddl-inherit.html^0|0|0|0|9|7|0|0|T|C|2K|E|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|11|8|@]|9|@$K|12|L|13|1|14]]|A|$]]|$1|M|3|N|5|6|7|15|8|@$K|16|L|17|O|P]|$K|18|L|19|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

Not necessarily what you asked, but maybe using postgres inheritance might help?

<pre><code>CREATE TABLE A (
 ID int,
 column1 text,
 column2 text,
 column3 text
);

CREATE TABLE B (
 column4 text
) INHERITS (A);
</code></pre>

This avoids the need to update B. 

But be sure to read all the <a href="http://www.postgresql.org/docs/8.4/static/ddl-inherit.html" rel="noreferrer">details</a>.

Otherwise, what you ask for is not considered a good practice - dynamic stuff such as views with <code>SELECT * ...</code> are discouraged (as such slight convenience might break more things than help things), and what you ask for would be equivalent for the <code>UPDATE ... SET</code> command.

blocks|key|2017776|text|您可以使用非标准的FROM子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2017777|UPDATE+b
SET+column1+=+a.column1,
++column2+=+a.column2,
++column3+=+a.column3
FROM+a
WHERE+a.id+=+b.id
AND+b.id+=+1|code-block|syntax|javascript|2017778|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/8.4/static/sql-update.html^0|9|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

You can use the non-standard <a href="http://www.postgresql.org/docs/8.4/static/sql-update.html" rel="noreferrer">FROM</a> clause.

<pre><code>UPDATE b
SET column1 = a.column1,
 column2 = a.column2,
 column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1
</code></pre>

blocks|key|2017816|text|这是一个很大的帮助。代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2017817|UPDATE+tbl_b+b
SET+++(++column1,+++column2,+++column3)
++++=+(a.column1,+a.column2,+a.column3)
FROM+++tbl_a+a
WHERE++b.id+=+1
AND++++a.id+=+b.id;|code-block|syntax|javascript|2017818|效果很好。|2017819|请注意，您需要在|2017820|From+"tbl_a"+a|2017821|才能让它工作。|2017822|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This is a great help. The code 

<pre><code>UPDATE tbl_b b
SET ( column1, column2, column3)
 = (a.column1, a.column2, a.column3)
FROM tbl_a a
WHERE b.id = 1
AND a.id = b.id;
</code></pre>

works perfectly.

noted that you need a bracket "" in 

<pre><code>From "tbl_a" a
</code></pre>

to make it work.

I have two tables:

<pre><code>A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]
</code></pre>

<code>A</code> will always be subset of <code>B</code> (meaning all columns of <code>A</code> are also in <code>B</code>).

I want to update a record with a specific <code>ID</code> in <code>B</code> with their data from <code>A</code> for all columns of <code>A</code>. This <code>ID</code> exists both in <code>A</code> and <code>B</code>.

Is there an <code>UPDATE</code> syntax or any other way to do that without specifying the column names, just saying "set all columns of A"?

I'm using PostgreSQL, so a specific non-standard command is also accepted (however, not preferred).

SQL update fields of one table from fields of another one

PostgreSQL

我有两张表：A [ID, column1, column2, column3]B [ID, column1, column2, column3, column4]A将始终是B的子集(这意味着A的所有列也在B中)。我想用B中的特定ID更新一条记录，该记录包含来自A的A所有列的数据。此ID在A和B中都存在。有没有一种UPD...

问SQL从一个表的字段更新另一个表的字段
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL从一个表的字段更新另一个表的字段EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL从一个表的字段更新另一个表的字段
EN