Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-27
4
Medium Priority
?
6,575 Views
Last Modified: 2016-02-10
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
Comment
Question by:Jim Horn
  • 3
4 Comments
 
LVL 66

Author Comment

by:Jim Horn
ID: 39958677

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
 
LVL 36

Accepted Solution

by:
ste5an earned 2000 total points
ID: 39958746
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
 
LVL 66

Author Comment

by:Jim Horn
ID: 39958763
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
 
LVL 66

Author Closing Comment

by:Jim Horn
ID: 39958764
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question