update that uses field from child table


  • I want to update the fact that I have printed a member cards for new memberships baseed on the date of a membership in a child table.

    update members set cardprinted='Y' from select m.*, c.* from members as m join memberships c on m.memberid= c.memberid where c.join_date > '11/7/2013'

    This gives an error, and I can't find any examples that show how to do this. Take the "update members set carprinted='Y' from out and it pulls up the members correctly that need to have a new member cards that have been printed.

    Thanks in advance.

    • Edited by David32 Friday, November 08, 2013 5:38 PM
    Friday, November 08, 2013 5:35 PM

All replies

  • try:

    update memb set cardprinted = 'Y'
    from dbo.members as memb inner join dbo.memberships as mship
    on memb.memberid = c.memberid
    where c.join_date > '20131107';

    In the future, writing "this gives an error" is generally useless information.  Always post the exact and complete error message. 

    Friday, November 08, 2013 6:41 PM
  • I tried your answer with couple of corrections for typing error on my part,

    update memb set print_card= 'Y' from dbo.members as memb inner join dbo.membersip as mship on memb.memberid= mship.memberid where mship.join_date > '20131207'

    I receive the error,

    Column or expression 'print_card' cannot be updated.

    then is says

    The multi-part identifier 'memb.memberid' could not be bound.

    • Edited by David32 Monday, December 16, 2013 11:15 PM
    Monday, December 16, 2013 11:15 PM