Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: problem with an UPDATE...

    trying to create an UPDATE but am getting and error.
    "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

    Code:
    update XAPCHECKS
    set xapck_amt = 
    (select sum(apph_paymnts), * from APPHISTF 
    LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and apt_type = apph_type and apt_id = apph_id
    LEFT JOIN APBANKF ON apb_code = apt_bank 
    left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
    where (apph_comp = '01') and (apph_vend = '1010') and 
    	xapck_check = apph_payck and xapck_chk_type = (CASE chm_type WHEN null THEN ' ' ELSE chm_type END) and xapck_check_status = (CASE chm_stat when null then ' ' ELSE chm_stat END)
    	and xapck_bank = apt_bank
    GROUP by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id, apph_paymnts)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is here --

    set xapck_amt = (select sum(apph_paymnts), *

    the error says the subquery has more than one column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Posts
    71

    when i take that out i get this error...

    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated."

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, that's because the subquery used in a SET can return only one column, one row

    it's called a scalar subquery because it's supposed to return only a single scalar value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Posts
    71

    i got it by removing the group by clause.

    not sure why i had that in there but it seems to working ok.
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •