通过SQL查询创建表 [英] Create table from SQL query

问题描述

这是一个令人烦恼的问题，我不知道该如何解决.我正在使用Microsoft SQL Server 2008.

This is one annoying issue and I can't figure out how to solve it. I'm Using Microsoft SQL Server 2008.

所以我有两个表，我需要更新两个表.他们共享一个公用密钥，例如id.我想用一些东西更新Table1，然后更新分别在Table1中修改的Table2行.

So I have two tables and I need to update both of them. They share a common key, say id. I want to update Table1 with some stuff and then update the Table2 rows which were respectively modified in Table1.

问题是我不太了解修改了哪些行，因为我是用ORDER BY NEWID()随机选择它们的，所以我可能无法以任何方式在Table2上使用JOIN.我正在尝试保存在查询中为Table1修改的必要详细信息，并将其传递给Table2

The issue is that I don't quite know which rows were modified, because I'm picking them randomly with ORDER BY NEWID() so I probably cannot use a JOIN on Table2 in any way. I am trying to save the necessary details which were modified in my query for Table1 and pass them to Table2

这就是我想要做的

CREATE TABLE IDS (id int not null, secondid int)

SELECT [Table1].[id], [Table1].[secondid]
INTO IDS
FROM
(
UPDATE [Table1]
SET [secondid]=100
FROM [Table1] t
WHERE t.[id] IN 
    (SELECT TOP 100 PERCENT t.[id] FROM [Table1]
        WHERE (SOME_CONDITION)
        ORDER BY NEWID()
    ) 
)

UPDATE [Table2]
SET some_column=i.secondid
FROM [Table2] JOIN IDS i ON i.id = [Table2].[id]

但是我明白了

关键字"UPDATE"附近的语法不正确.

Incorrect syntax near the keyword 'UPDATE'.

所以问题是:我该如何解决语法错误，或者这是一种更好的方法?

So the question is: how can I solve the syntax error or is it a better way to do this?

注意:在此新要求之前，第一个FROM括号之间的查询运行良好，因此我怀疑其中是否存在问题.也许吧?

Note: the query enclosed between the parentheses of the first FROM worked well before this new requirement, so I doubt there's a problem in there. Or maybe?

编辑:按照skk的建议更改第二个UPDATE仍然会导致相同的错误(恰好在包含UPDATE的以下行中):

EDIT: Changing the second UPDATE as skk suggested still leads to the same error (on exactly the below line which contains UPDATE):

UPDATE [Table2]
SET some_column=i.secondid
FROM [Task] JOIN IDS i on i.[id]=[Table2].[id]
WHERE i.id=some_value

推荐答案

这是在抱怨，因为您没有为紧接在UPDATE [Table2]之前的第一个查询中使用的派生表加上别名.

It's complaining because you aren't aliasing the derived table used in the first query, immediately preceding UPDATE [Table2].

如果添加别名，则会收到其他错误:

If you add an alias, you'll get a different error:

嵌套的INSERT，UPDATE，DELETE或MERGE语句必须具有OUTPUT子句.

A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause.

这将返回到@Adam Wenger的答案.

Which leads back to @Adam Wenger's answer.

不确定我是否完全了解您要执行的操作，但是将执行以下sql(替换SOME_CONDITION之后):

Not sure I completely understand what you are trying to do, but the following sql will execute (after replacing SOME_CONDITION):

CREATE TABLE IDS (id int not null, secondid int)

UPDATE t SET [secondid] = 100
OUTPUT inserted.[id], inserted.[secondid] into [IDS]
FROM [Table1] t
WHERE t.[Id] IN
    (
        SELECT TOP 100 PERCENT t.[id] from [Table1]
        WHERE (SOME_CONDITION)
        ORDER BY NEWID()
    )

UPDATE [Table2]
SET some_column = i.secondid
FROM [Table2] JOIN IDS i ON i.id = [Table2].[id]

这篇关于通过SQL查询创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！