Solved

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

Posted on 2014-03-27
4
6,501 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

617 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