mysql - 与其他列更新

Question

我想用另一列更新一列...

我的代码:

update Table1 set Table1.number = Table2.number from Table1 where Table2.Table1Id= Table1.id;

错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from Table1 where Table2.Table1Id= Table1.id' at line 1

Answer 1

执行此更新的 ANSI 方法是:

UPDATE Table1 
SET     Table1.number = (   SELECT  Table2.number
                            FROM    Table2
                            WHERE   Table2.Table1Id= Table1.id
                        )
WHERE   EXISTS
        (   SELECT  Table2.number
            FROM    Table2
            WHERE   Table2.Table1Id= Table1.id
        );

或者专有的 MySQL UPDATE/JOIN 语法是:

UPDATE  Table1 
        INNER JOIN Table2
            ON Table2.Table1Id= Table1.id;
SET     Table1.number = Table2.number;

这里的 ANSI 语法非常糟糕，特别是当您需要更新多个字段时，例如:

UPDATE Table1 
SET     Table1.number = (   SELECT  Table2.number
                            FROM    Table2
                            WHERE   Table2.Table1Id= Table1.id
                        ),
        Table1.number2 = (  SELECT  Table2.number2
                            FROM    Table2
                            WHERE   Table2.Table1Id= Table1.id
                        ),
        Table1.number3 = (  SELECT  Table2.number3
                            FROM    Table2
                            WHERE   Table2.Table1Id= Table1.id
                        )
WHERE   EXISTS
        (   SELECT  Table2.number
            FROM    Table2
            WHERE   Table2.Table1Id= Table1.id
        );

我认为即使是那些坚持 ANSI 兼容代码的最坚定的人也很难通过简单的连接来证明这一点!