update error. Subquery returned more than 1 value

• reza.asali

  SSC Enthusiast

  Points: 176

  More actions

  October 8, 2024 at 7:01 pm

  #4466525

  Hi friends. how can i fix that?

  ;with cte

  as

  (select distinct(e.Id )

  from SecurityKmsAbfa..EnterprisePositions e

  join SecurityKmsAbfa..Import_UsersTeh i on e.Name=i.vahedeSazmani

  join NewKmsAbfa..Members m on m.SSN=i.kodeMelli

  )

  update SecurityKmsAbfa..users

  set EnterprisePositionId = (select id from cte )

  from SecurityKmsAbfa..Import_UsersTeh i

  join NewKmsAbfa..Members m on m.SSN=i.kodeMelli

  join SecurityKmsAbfa..users u on u.Id=m.id

  where u.EnterprisePositionId is null

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  October 8, 2024 at 7:52 pm

  #4466533

  If the following query produces more than one result, that is your problem – you cannot update a single value to a multi-valued result set:

  SELECT DISTINCT
         (e.Id)
  FROM SecurityKmsAbfa..EnterprisePositions e
      JOIN SecurityKmsAbfa..Import_UsersTeh i
          ON e.Name = i.vahedeSazmani
      JOIN NewKmsAbfa..Members m
          ON m.SSN = i.kodeMelli

  Note that the double-dot notation is considered bad practice. Unless you have a reason (other than laziness) for omitting the schema name, it should be included.

  If you tell us what you are trying to do, we might be able to fix your query, but as there are a few tables involved, I'd rather not guess.

  ---