Error sub query return more than one value

Question

Error sub query return more than one value

SSC Enthusiast

Points: 116

November 5, 2024 at 6:52 am

insert into NewKms8081..members
SELECT
       (select u2.Id from SecurityKms8081..users u2  
join SecurityKms8081..Import_users_barghGharb i2 on i2.code_personeli=u2.PersonnelNumber

 )  as [Id]
           , NULL as [SSN]
           , NULL as [IdentityNumber]
           , NULL as [BirthdayYear]
           , NULL as [Address]
           , NULL as [PhoneNumber2]
           , NULL as [PhoneNumber3]
           , NULL as [EmploymentDate]
           , NULL as [LastEducationalCertificationId]
           --, (select u2.Id from SecurityKms8081..users u2 where u2.PersonnelNumber=i.code_personeli ) as [MemberActivityInfoId]
  , NEWID() as [MemberActivityInfoId]
           --, (select u2.Id from SecurityKms8081..users u2 where u2.PersonnelNumber=i.code_personeli ) as [UserDataId]
  ,  NEWID() as [UserDataId]
           , NULL as [PhotoPath]
           , '0DF87534-A229-4635-BF58-FC219F7A8E4E' as [OrganizationId]
           , NULL as [AttachmentPictureId]
           , NULL as [VisitId]
  FROM  SecurityKms8081..Import_users_barghGharb i
  join SecurityKms8081..users u on i.code_personeli=u.PersonnelNumber
  where u.Id not in
  (
    select m.id from NewKms8081..members m
  )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Your subquery does not make sense as it is not related to the from clause of your query.

(and because it performs the same query as your from clause, it will indeed return more than a single row, wich is not allowed by your construction)

Why don't you use u.Id ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Zidar SSC Enthusiast Points: 126 More actions · Answer 2

The first subquery likely returns more than one row, and it should return one row only. Check it.

insert into NewKms8081..members
SELECT
       (select u2.Id from SecurityKms8081..users u2  
join SecurityKms8081..Import_users_barghGharb i2 on i2.code_personeli=u2.PersonnelNumber

 )  as [Id]

The statement at the end should be OK,

  FROM  SecurityKms8081..Import_users_barghGharb i
  join SecurityKms8081..users u on i.code_personeli=u.PersonnelNumber
  where u.Id not in
  (
    select m.id from NewKms8081..members m
  )

it may return more than one row.

I hope this helped.

Zidar's Theorem: The best code is no code at all...