MySql - 使用同一个表中的选择语句更新表

MySql - Update table using select statment from same table

提问by user1368392

I'm trying to update row in a table using values from a different row (and different columns) in the same table. Something along the lines of this, although my syntax produces no results: Here is the code (updated):

我正在尝试使用来自同一表中不同行（和不同列）的值更新表中的行。尽管我的语法没有产生任何结果，但与此类似：这是代码（已更新）：

UPDATE table1 AS t1 INNER JOIN
(SELECT field_id_46,field_id_47 FROM table1 WHERE entry_id = 36) AS t2
SET t1.field_id_60 = t2.field_id_46, t1.field_id_61 = t2.field_id_47
WHERE t1.entry_id = 45;

回答by Nicola Cossu

update table as t1
inner join (
select field_id_46,field_id_47 from table where entry_id = 36) as t2
set t1.field_id_60 = t2.field_id_46,
    t1.field_id_61 = t2.field_id_47
where t1.entry_id = 45

or, simply

或者，简单地

update table as t1,
(
select field_id_46,field_id_47 from table where entry_id = 36) as t2
set t1.field_id_60 = t2.field_id_46,
    t1.field_id_61 = t2.field_id_47
where t1.entry_id = 45

回答by Diego

Adding..

添加..

Same tables, with more of one registers

相同的表，有多个寄存器

UPDATE table t1
INNER JOIN table t2 ON t2.entry_id = t1.entry_id
SET t1.field_id_60 = t2.field_id_60,
    t1.field_id_61 = t2.field_id_61

回答by krishna Prasad

You can update using inner join as follow :

您可以使用内部联接进行更新，如下所示：

UPDATE table1 AS t1 
    INNER JOIN table1 AS t2 
    SET t1.field_id_60 = t2.field_id_46, 
        t1.field_id_61 = t2.field_id_47 
WHERE t1.entry_id = 54;

回答by xtsoler

I found this question very useful because I was trying to insert into a table manually while that specific database was using a hibernate_sequence table. I used the solution from this question to mod my import script. I had a script with many "insert into" statements one after the other and I had to set the id manually. for example:

我发现这个问题非常有用，因为我试图在特定数据库使用 hibernate_sequence 表时手动插入表。我使用这个问题的解决方案来修改我的导入脚本。我有一个脚本，其中一个接一个地包含许多“插入”语句，我不得不手动设置 ID。例如：

insert into T01_REGIONS (ID, NAME) VALUES ({the next id from hibernate_sequence}, 'name1');
insert into T01_REGIONS (ID, NAME) VALUES ({the next id from hibernate_sequence}, 'name2');
..
.

So what I did is the following to work around my problem:

所以我所做的是以下解决我的问题：

insert into T01_REGIONS (ID, NAME) VALUES ((select next_val from hibernate_sequence limit 1), 'name1');update hibernate_sequence as t1, (select next_val+1 as next from hibernate_sequence limit 1) as t2 set t1.next_val = t2.next;
insert into T01_REGIONS (ID, NAME) VALUES ((select next_val from hibernate_sequence limit 1), 'name2');update hibernate_sequence as t1, (select next_val+1 as next from hibernate_sequence limit 1) as t2 set t1.next_val = t2.next;
..
.

Adding the extra query at the end of each line of my sql script was easy with notepad++. I know this might be very ugly but it did work for me in order to import data to a test hibernate operated mysql database while my data was coming from an oracle hibernate operated database.

使用记事本 ++ 在我的 sql 脚本的每一行末尾添加额外的查询很容易。我知道这可能非常难看，但它确实对我有用，以便在我的数据来自 oracle hibernate 操作的数据库时将数据导入测试 hibernate 操作的 mysql 数据库。

回答by Kaan Alvés K?l??

You are not need to this query

你不需要这个查询

SELECT field_id_46,field_id_47 FROM table WHERE entry_id = '36'

You should just do this:

你应该这样做：

UPDATE table SET (field_id_60,field_id_61) ('coming_value','other_value') WHERE entry_id = '45';

Also you can do this with 2 different coloumns.I think you can do like this.But It might i havent got any idea.You should split this query in which language do you use.In first method you should use this query.

你也可以用 2 个不同的 coloumns 来做到这一点。我认为你可以这样做。但我可能没有任何想法。你应该用你使用的语言拆分这个查询。在第一种方法中，你应该使用这个查询。

SELECT field_id_46,field_id_47 FROM table WHERE entry_id = '36'

And You can also return String that is coming from this data.Then you can use this returned value in update function.

并且您还可以返回来自此数据的字符串。然后您可以在更新函数中使用此返回值。