Error sub query return more than one value

  • 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
    )

  • 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

  • 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...

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

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