CODEWITHSUNDEEP
sqlsql-server-2008selectsql-update
Kaisar
Kaisar

Reputation: 35

Create table from SQL query

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

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.

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

This is what I'm trying to do

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]

But I get

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?

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?

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

Upvotes: 0

Views: 8001

Answers (3)

Jeff Ogata
Jeff Ogata

Reputation: 57793

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:

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

Which leads back to @Adam Wenger's answer.

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]

Upvotes: 1

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

The Update syntax is as follows

    UPDATE TableName SET ColumnName = Value WHERE {Condition}

but you have used FROM keyword also in that.

EDIT:

You change the code like follows and try again

UPDATE [Table2]  SET some_column=IDS.secondid WHERE  IDS.[id] = [Table2].[id] and 
IDS.id=some_value

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17540

Instead of creating a new table manually, SQL server has the OUTPUT clause to help with this

Upvotes: 3

Related Questions