Solved

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

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
PL/SQL query 14 51
SQL Inner Join Vs SubQueries 9 26
BULK INSERT most recent CSV 19 21
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now