question

Richie_Perkins avatar image
Richie_Perkins asked

Update a field from a query

Hello from a very new SQL user, im looking to update a field in a take which would be update dbo.AeFSState set dbo.AeFSState.FSState = 'ReleaseWait' where FSState = 'ErrorHandling' But I would like to create a query that would identify content in the dbo.AeFSState table that needs to be update, the query for finding the said content is SELECT [ProviderId], dbo.AeFSState.UID, [FSState], [LastErrorDetail] FROM [IdsData].[dbo].[AeFSState] inner join dbo.AeLastErrorDetail on dbo.AeFSState.UID = dbo.AeLastErrorDetail.UID inner join dbo.AeProviderId on dbo.AeFSState.UID = dbo.AeProviderId.UID where FSState = 'ErrorHandling' and ProviderId <> 'BIG' and ProviderId <> 'SMA' So ive added the 2 together and I get the following error SELECT [ProviderId], dbo.AeFSState.UID, [FSState], [LastErrorDetail] FROM [IdsData].[dbo].[AeFSState] inner join dbo.AeLastErrorDetail on dbo.AeFSState.UID = dbo.AeLastErrorDetail.UID inner join dbo.AeProviderId on dbo.AeFSState.UID = dbo.AeProviderId.UID update dbo.AeFSState set dbo.AeFSState.FSState = 'ReleaseWait' where FSState = 'ErrorHandling' and ProviderId <> 'BIG' and ProviderId <> 'SMA' Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'ProviderId'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'ProviderId'. From what I gather the 2nd part of the statement is trying to alter the dbo.AeFSState table and not my query so the question is how do I change the stetment to change the dbo.AeFSState.FSState field based on my query criteria, do i need to create a new table with my query output and then update that? Thank you for any insight Richie
queryupdate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
timreganmorris avatar image
timreganmorris answered
UPDATE dbo.AeFSState SET dbo.AeFSState.FSState = 'ReleaseWait' FROM [IdsData].[dbo].[AeFSState] INNER JOIN dbo.AeLastErrorDetail ON dbo.AeFSState.UID = dbo.AeLastErrorDetail.UID INNER JOIN dbo.AeProviderId ON dbo.AeFSState.UID = dbo.AeProviderId.UID WHERE FSState = 'ErrorHandling' AND ProviderId <> 'BIG' AND ProviderId <> 'SMA'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Richie_Perkins avatar image Richie_Perkins commented ·
timreganmorris Thank you its so easy when you know how, you have made my day as this not only helps me with this issue its also taught me. Thank you Richie
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.