MSSQL update multiple rows based on select statement

Question

I am trying to update multiple rows in one table, based on a select statement on another table.

This is my query:

UPDATE dbo.[user_message_content]
SET [status] = 1
WHERE [message_id] = (SELECT [message_id] FROM dbo.[user_message] WHERE [receiver_id] = @userID)
AND [status] = 0

This select statement may return multiple rows, which leads me to this error:

Msg 512, Level 16, State 1, Procedure usp_profileUserMessageMarkAsRead, Line 11 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What is the correct way to achieve what I need?

Thanks very much

ypercubeᵀᴹ · Accepted Answer · 2011-09-01 07:29:55Z

5

If you want to update all those records, change the = to IN:

UPDATE dbo.[user_message_content]
SET [status] = 1
WHERE [message_id] IN 
    ( SELECT [message_id] FROM dbo.[user_message] WHERE [receiver_id] = @userID )
AND [status] = 0

answered Sep 1, 2011 at 7:29

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Igor Borisenko · Accepted Answer · 2011-09-01 07:38:43Z

1

You can also use UPDATE with FROM clause http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx.

USE tempdb; 
    GO

    create table #user_message_content([status] int, message_id int)
    create table #user_message (message_id int,receiver_id int)

    declare @UserID int

    UPDATE mc
    SET mc.[status] = 1
    FROM #user_message_content mc join #user_message m on mc.message_id = m.message_id 
    WHERE m.receiver_id = @userID
          AND mc.[status]=0;

    drop table #user_message_content
    drop table #user_message

answered Sep 1, 2011 at 7:38

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

Comments

Nicolas Renold · Accepted Answer · 2011-09-01 07:23:31Z

0

I think you need to use a join to do this

USE dbo; // Sets the current database to dbo, I did this for readability

UPDATE user_message_content join user_message on user_message_content.message_id = user_message.message_id 
SET user_message_content.status = 1
WHERE user_message.receiver_id = @userID;

answered Sep 1, 2011 at 7:23

Nicolas Renold

5552 silver badges5 bronze badges

Collectives™ on Stack Overflow

MSSQL update multiple rows based on select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related