[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6632
  • Last Modified:

UPDATE with JOIN returning a 'Subquery returned more than 1 value.' error

Hi All

I am trying to run an update query:  
The source is table ssis_badge_tracking, which is a SSIS staging table.
The destination is view empmaster, which is a single-table view with one update trigger (see first comment).

Both tables have empnum as a unique column, meaning when I run the below T-SQL there are no rows returned.
-- Duplicate row check
SELECT empnum, count(empnum) as the_count
FROM empmaster
GROUP BY empnum
HAVING COUNT(empnum) > 1
ORDER BY empnum

SELECT empnum, count(empnum) as the_count
FROM ssis_badge_tracking
GROUP BY empnum
HAVING COUNT(empnum) > 1
ORDER BY empnum

Open in new window

My problem: When I execute the below T-SQL, an error is generated:
UPDATE e
 SET 
   e.deptid = bt.deptid, 
   e.ft_code = bt.ft_code, 
   e.title = bt.title, 
   e.firstname = bt.firstname, 
   e.middlename = bt.middlename, 

   e.middle = bt.middle, 
   e.lastname = bt.lastname, 
   e.birthdate = bt.birthdate, 
   e.gender = bt.gender, 
   e.ssn = bt.ssn, 

   e.empstatus = bt.empstatus, 
   e.rehiredate = bt.rehiredate, 
   e.sendate = bt.sendate, 
   e.addr_1 = bt.addr_1, 
   e.addr_2 = bt.addr_2, 

   e.addr_city = bt.addr_city, 
   e.addr_state = bt.addr_state, 
   e.addr_zip = bt.addr_zip, 

   e.phone1 = bt.phone1
FROM ssis_badge_tracking bt
   JOIN bt empmaster e ON bt.empnum = e.empnum 
WHERE bt.is_update = 1

Open in new window

Msg 512, Level 16, State 1, Procedure upd_empmaster, Line 57
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.

Open in new window

Just for kicks and giggles, when I run the below, the count returns 747, and the second query, with all columns from both tables, returns 747 rows.   So I can't identify a single empnum in one table that relates to multiple empnum's in the other table.
SELECT COUNT(*) FROM  ssis_badge_tracking WHERE is_update = 1 

SELECT  bt.empnum,e.empnum ,
   e.deptid,bt.deptid, 
   e.ft_code,bt.ft_code, 
   e.title,bt.title, 
   e.firstname,bt.firstname, 
   e.middlename,bt.middlename, 

   e.middle,bt.middle, 
   e.lastname,bt.lastname, 
   e.birthdate,bt.birthdate, 
   e.gender,bt.gender, 
   e.ssn,bt.ssn, 

   e.empstatus,bt.empstatus, 
   e.rehiredate,bt.rehiredate, 
   e.sendate,bt.sendate, 
   e.addr_1,bt.addr_1, 
   e.addr_2,bt.addr_2, 

   e.addr_city,bt.addr_city, 
   e.addr_state,bt.addr_state, 
   e.addr_zip,bt.addr_zip, 

   e.phone1,bt.phone1
FROM ssis_badge_tracking bt 
   LEFT JOIN empmaster e ON bt.empnum = e.empnum 
WHERE bt.is_update = 1

Open in new window

0
Jim Horn
Asked:
Jim Horn
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:

CORRECTION

Table empl (the one table in view empmaster) has one update trigger, which when I disable it, the UPDATE statement works correctly.
USE [db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[upd_empmaster]
ON [dbo].[empl]
AFTER UPDATE
AS
DECLARE
 @RC int,
@post_badgenum CHAR(15),
	@post_badge_issue datetime,
	@post_badge_return datetime,
	@post_badge_comment char(255),
	@pre_empid int,
	@pre_empnum int,
	@pre_org_id int,
	@pre_lastname char(15),
	@pre_firstname char(15),
	@pre_middle char(1),
	@pre_emailaddr char(25),
	@pre_birthdate datetime,
	@pre_empstatus char(25),
	@pre_sendate datetime,
	@pre_termdate datetime,
	@pre_addr_1 char(30),
	@pre_addr_2 char(30),
	@pre_addr_city char(25),
	@pre_addr_state char(2),
	@pre_addr_zip char(10),
	@pre_phone1 char(14),
	@pre_badgenum char(15),
	@pre_badge_issue datetime,
	@pre_badge_return datetime,
	@pre_badge_comment char(255),
	@pre_hiredate datetime,
	@pre_rehiredate datetime,
	@pre_zxuser char(20),
	@pre_dtstamp datetime,
	@pre_manlock int,
	@pre_userid int,
	@pre_nr_status char(25),
	@pre_show_bday char(1),
	@pre_pass_per_year int,
	@pre_comp_expire datetime, 
	@pre_gsc_expire datetime, 
	@pre_sida_expire datetime, 
	@pre_hidcardid char(20), 
	@pre_nr_sendate datetime,
	@pre_middlename char(20),
	@pre_gender char(1),
	@pre_country char(20),
	@pre_ssn char(9),
	@pre_tc_issue datetime,
	@pre_tc_expired datetime,
	@pre_sida_return datetime

begin
-- pre = deleted
-- post = inserted
set @post_badgenum = (select [bdg_num] from inserted)
set	@post_badge_issue = (select [bdg_iss_dte] from inserted)
set	@post_badge_return = ( select [bdg_rtrn_dte] from inserted)
set	@post_badge_comment = (select [bdg_cmnt_txt] from inserted)
set	@pre_empid = (select [empl_id] from deleted)
set @pre_empnum = (select [empl_num] from deleted)
set	@pre_org_id = (select [org_id] from deleted)
set	@pre_lastname = (select [lst_nme] from deleted)
set	@pre_firstname = (select [fst_nme] from deleted)
set	@pre_middle = (select [mddl_init_nme] from deleted)
set	@pre_emailaddr = (select [eml_addr] from deleted)
set	@pre_birthdate = (select [brth_dte] from deleted)
set	@pre_empstatus = (select [empl_stat_cde] from deleted)
set	@pre_sendate = (select [snr_dte] from deleted)
set	@pre_termdate = (select [trmn_dte] from deleted)
set	@pre_addr_1 = (select [line_1_addr] from deleted)
set	@pre_addr_2 = (select [line_2_addr] from deleted)
set	@pre_addr_city = (select [cty_nme] from deleted)
set	@pre_addr_state = (select [state_cde] from deleted)
set	@pre_addr_zip = (select [pstl_cde] from deleted)
set	@pre_phone1 = (select [phn_num] from deleted)
set	@pre_badgenum = (select [bdg_num] from deleted)
set	@pre_badge_issue = (select [bdg_iss_dte] from deleted)
set	@pre_badge_return = (select [bdg_rtrn_dte] from deleted)
set	@pre_badge_comment = (select [bdg_cmnt_txt] from deleted)
set	@pre_hiredate = (select [hire_dte] from deleted)
set	@pre_rehiredate = (select [re_hire_dte] from deleted)
set	@pre_zxuser = (select [lst_upd_usr_id] from deleted)
set	@pre_dtstamp = (select [lst_upd_tms] from deleted)
set	@pre_manlock = (select 0 from deleted)
set	@pre_userid = (select [usr_id] from deleted)
set	@pre_nr_status = (select [non_rvn_stat_cde] from deleted)
set	@pre_show_bday = (select [brth_day_shw_ind] from deleted)
set	@pre_pass_per_year = (select [pass_per_yr_cnt] from deleted)
set	@pre_comp_expire = (select [bdg_expr_dte] from deleted)
set	@pre_gsc_expire =  (select [gsc_bldg_expr_dte] from deleted)
set	@pre_sida_expire =  (select [sida_bdg_expr_dte] from deleted)
set	@pre_hidcardid =  (select [hid_bldg_accss_crd_id] from deleted)
set	@pre_nr_sendate = (select [non_rvn_snr_dte] from deleted)
set	@pre_middlename = (select [mddl_nme] from deleted)
set	@pre_gender = (select [gndr_cde] from deleted)
set	@pre_country = (select [cntry_nme] from deleted)
set	@pre_ssn = (select [scl_scrty_num] from deleted)
set	@pre_tc_issue = (select [trvl_crd_iss_dte] from deleted)
set	@pre_tc_expired = (select [trvl_crd_expr_dte] from deleted)
set	@pre_sida_return = (select [sida_bdg_rtrn_dte] from deleted);


EXECUTE @RC = [dbo].[updempmaster] 
   @post_badgenum
  ,@post_badge_issue
  ,@post_badge_return
  ,@post_badge_comment
  ,@pre_empid
  ,@pre_empnum
  ,@pre_org_id
  ,@pre_lastname
  ,@pre_firstname
  ,@pre_middle
  ,@pre_emailaddr
  ,@pre_birthdate
  ,@pre_empstatus
  ,@pre_sendate
  ,@pre_termdate
  ,@pre_addr_1
  ,@pre_addr_2
  ,@pre_addr_city
  ,@pre_addr_state
  ,@pre_addr_zip
  ,@pre_phone1
  ,@pre_badgenum
  ,@pre_badge_issue
  ,@pre_badge_return
  ,@pre_badge_comment
  ,@pre_hiredate
  ,@pre_rehiredate
  ,@pre_zxuser
  ,@pre_dtstamp
  ,@pre_manlock
  ,@pre_userid
  ,@pre_nr_status
  ,@pre_show_bday
  ,@pre_pass_per_year
  ,@pre_comp_expire
  ,@pre_gsc_expire
  ,@pre_sida_expire
  ,@pre_hidcardid
  ,@pre_nr_sendate
  ,@pre_middlename
  ,@pre_gender
  ,@pre_country
  ,@pre_ssn
  ,@pre_tc_issue
  ,@pre_tc_expired
  ,@pre_sida_return

end;

Open in new window

0
 
ste5anSenior DeveloperCommented:
Triggers are executed per statement, thus they need to work on sets not no a single row/value. So these SELECT statements returns sets and the assignment fails:

 SET @post_badgenum = ( SELECT   [bdg_num]
                           FROM     inserted
                         );

Open in new window


Either you rewrite your [dbo].[updempmaster] procedure to accept table parameters or you need a cursor. E.g. like this:

ALTER TRIGGER [dbo].[upd_empmaster] ON [dbo].[empl]
    AFTER UPDATE
AS
    SET NOCOUNT ON;

    DECLARE @pre_badgenum CHAR(15) ,
        @post_badgenum CHAR(15);

    DECLARE update_cursor CURSOR
    FOR
        SELECT  I.[bdg_num] AS pre_badgenum ,
                D.[bdg_num] AS post_badgenum
        FROM    INSERTED I
                INNER JOIN DELETED D ON I.PrimaryKeyColumns = D.PrimaryKeyColumns;

    OPEN update_cursor;
    FETCH NEXT FROM update_cursor INTO @pre_badgenum, @post_badgenum;
	
    WHILE @@FETCH_STATUS = 0
        BEGIN	
            EXECUTE [dbo].[updempmaster] @pre_badgenum, @post_badgenum;

            FETCH NEXT FROM update_cursor INTO @pre_badgenum, @post_badgenum;
        END;

    CLOSE update_cursor;
    DEALLOCATE update_cursor;

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks for the diagnosis and the trigger code.

I've decided I'm not touching the trigger, as who the hell knows what other process depend on it and would break if I changed it.

So instead of the single UPDATE statement, I'm going to throw it in a cursor and have it execute row-by-row.   The number of rows we're talking on a daily basis will not be greater than 500, so I'll accept any decrease in performance with the cursor.

Thanks.
Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now