Solved

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

Posted on 2014-03-27
4
6,457 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 65

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 33

Accepted Solution

by:
ste5an earned 500 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 65

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 65

Author Closing Comment

by:Jim Horn
ID: 39958764
Thanks
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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