CODEWITHSUNDEEP
sqlsql-server
misguided
misguided

Reputation: 3789

Subquery returned more than 1 value not allowed when the subquery is used as an expression

My aim is to take data from one table and update it in another table. I am doing an explicit join and running the query below.

UPDATE [Users] set
  name = (
    SELECT concat(Column1,' ',Column2) 
    from [Table2] 
    inner join [Users] on Name = Column3
    where Name like 'ABC'
  )

I get the following error when I run the above query:

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

Please note Column1, Column2, Column3 are unique to Table 2, hence I have not added a qualifier.

Upvotes: 0

Views: 69

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use UPDATE... JOIN

UPDATE  t1 set
  name = concat(Column1,' ',Column2) 
FROM [Users] t1 JOIN [Table2] on Name = Column3
where Name like 'ABC'

Upvotes: 1

Related Questions