~~Please help: Select and Update~~

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

~~Please help: Select and Update~~

Author

Topic

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-24 : 22:22:03

Hey boys and girls. I am very frustrated. I have used cursors for a while. I have came across a situation where I have to use a single single sql statement and I am not having any luck. So I thought I would reach our and see if anyone could help me.

So here is the situation. I will first post my cursor sp.


CREATE PROCEDURE sp_Update01212 @EmailAddy AS varchar(150)  AS	
	declare @comp varchar(5), @emp varchar(20), @fieldname varchar(15), @attendvalue varchar(5), @historyvalue varchar(5), @statusvalue int, 
                @timeseq int, @userfield2value char(10), @updated char(1), @EMAIL_ADDR varchar(150), @SUBJECT varchar(150), @MESSAGE varchar(500)
	
	declare CurQry cursor for
		select COMPANY, EMPLOYEE, TIME_SEQ, FIELD_NAME, STATUS, ATTEND_CODE, HISTORY, USERFIELD2, UPDATED from user01.TABLEZ where UPDATED != 'Y'
	OPEN CurQry
	FETCH NEXT FROM CurQry 
		INTO @comp, @emp, @timeseq, @fieldname, @statusvalue, @attendvalue, @historyvalue, @userfield2value, @updated
	WHILE @@FETCH_STATUS = 0
		BEGIN
		IF @fieldname = 'ATTEND_CODE'
		BEGIN
			UPDATE user01.TABLEP set ATTEND_CODE=@attendvalue, PPTUSR3_SS_SW='N' WHERE COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq
		END
		IF @fieldname = 'HISTORY'
		BEGIN
			UPDATE user01.TABLEP set HISTORY=@historyvalue, PPTUSR4_SS_SW='N' WHERE COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq 
		END
		IF @fieldname = 'STATUS'
		BEGIN
			UPDATE user01.TABLEP set STATUS=@statusvalue WHERE COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq
			IF @statusvalue >= 5
			BEGIN
				UPDATE user01.TABLEP set PPTUSR1_SS_SW='N', PPTUSR2_SS_SW='N' WHERE COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq
			END
		END
		IF @fieldname = 'USERFIELD2'
		BEGIN
			UPDATE user01.TABLEP set USERFIELD2=@userfield2value WHERE COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq
		END
		UPDATE user01.TABLEZ set UPDATED = 'Y' where COMPANY=@comp AND EMPLOYEE=@emp AND TIME_SEQ=@timeseq AND FIELD_NAME=@fieldname
		
	FETCH NEXT FROM CurQry 
		INTO @comp, @emp, @timeseq, @fieldname, @statusvalue, @attendvalue, @historyvalue, @userfield2value, @updated
	END
	CLOSE CurQry
	DEALLOCATE CurQry
	IF	LEN(RTRIM(@EmailAddy)) > 0 
	BEGIN
		SET @SUBJECT = 'Subject Test'
		SET @MESSAGE = 'Message Test'
		EXEC user05.sp_Mail 'NoReply@mycompany.com', @EmailAddy, @SUBJECT, @MESSAGE
	END
GO

The above works great but when put out to an ASP page it causes the page to time out. So I want to do it all in one single sql statement. There are two tables TABLEZ and TABLEP. There are 4 fields that are updated. They are stored in TABLEZ and then in one process moved over to TABLEP. The 4 fields that could have updates are STATUS, ATTEND_CODE, HISTORY, and USERFIELD2. TABLEZ has these fields plus a field called FIELD_NAME. FIELD_NAME will have a value like 'STATUS'. Then on that same record the field STATUS will have a value. That value is what gets updated to TABLEP. So each record in TABLEZ only updates one field in TABLEP.

Basically it should work like this.

-Look at TABLEZ for any records in which the field UPDATE != 'Y'
-Based on the FIELD_NAME field in TABLEZ update the field in TABLEP (if STATUS is the field that is being updated and it is above 5 do additional updates as shown above.)
-Finally update the records in TABLEZ that have been processed.

I just can't get it to work or even begin to imagine how I would do it in one statement.

Any help is greatly appreciated.

Thank you,

-Jessica

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-08-25 : 00:47:26

hi Jessica,

Try the following Update,

UPDATE P
SET
ATTEND_CODE = CASE WHEN Z.FIELD_NAME = 'ATTEND_CODE' THEN Z.ATTEND_CODE ELSE P.ATTEND_CODE END,
PPTUSR3_SS_SW = CASE WHEN Z.FIELD_NAME = 'ATTEND_CODE' THEN 'N' ELSE P.PPTUSR3_SS_SW END,
HISTORY = CASE WHEN Z.FIELD_NAME = 'HISTORY' THEN Z.HISTORY ELSE P.HISTORY END,
PPTUSR4_SS_SW = CASE WHEN Z.FIELD_NAME = 'HISTORY' THEN 'N' ELSE P.PPTUSR4_SS_SW END,
STATUS = CASE WHEN Z.FIELD_NAME = 'STATUS' THEN Z.STATUS ELSE P.STATUS END,
PPTUSR1_SS_SW = CASE WHEN Z.FIELD_NAME = 'STATUS' AND Z.STATUS >= 5 THEN 'N' ELSE P.PPTUSR1_SS_SW END,
PPTUSR2_SS_SW = CASE WHEN Z.FIELD_NAME = 'STATUS' AND Z.STATUS >= 5 THEN 'N' ELSE P.PPTUSR2_SS_SW END,
USERFIELD2 = CASE WHEN Z.FIELD_NAME = 'USERFIELD2' THEN Z.USERFIELD2 ELSE P.USERFIELD2 END,
UPDATED = CASE WHEN Z.FIELD_NAME = P.FIELD_NAME THEN 'Y' ELSE P.UPDATED END

FROM user01.TABLEP P
INNER JOIN user01.TABLEZ Z ON P.COMPANY = Z.COMPANY
AND P.EMPLOYEE = Z.EMPLOYEE
AND P.TIME_SEQ = Z.TIME_SEQ
WHERE Z.UPDATED != 'Y'

If needed change it as per u r requirement..

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-25 : 18:17:32

Thank you so very much. This almost works and really helps me to make sense of the select and update. There is a problem though and maybe you or someone can help me solve it.

-First off there is not an UPDATED field on the P table. Sorry I should have explained that. So right now it will not update the Z.UPDATED flag

-Second the P table has the 4 update fields STATUS, HISTORY, USERFIELD2 and ATTEND_CODE but there is no P.FIELD_NAME. I think explaining this might lead into why I am only getting a few updates.

Those are the only two things so GREAT JOB! Even though it did not update the flag when I took that part of the code out I did get some updates. The problem is I am only getting 1 update per COMPANY, EMPLOYEE, TIME_SEQ. Here is an example of data I was testing from the Z table.


COMPANY   EMPLOYEE  TIME_SEQ    FIELD_NAME    STATUS     ATTEND_CODE  HISTORY  USERFIELD2   UPDATED
10	  1060	    2061266	ATTEND_CODE   0	         L 	      ''       ''	    ''      	 
10	  1060	    2061266	STATUS        7	  	 ''           ''       ''           ''
10	  1060	    2061266	USERFIELD2    0          ''	      ''       '8/23/2007'  '' 		
10	  1478	    2067218	STATUS        6	  	 ''           ''       ''           '' 
10	  1478	    2067218	USERFIELD2    0	         ''           ''       '8/22/2007'  ''

When the above processes with the new sql statement it only grabs the first record from each EMPLOYEE, COMPANY, and TIME_SEQ. So it should do 5 updates in the above case but it only does 2.

So if you or someone could be so kind to help me get the Z.UPDATED to update and all the records to process I will be very very appreciative.

Thank you,

-Jessica

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-25 : 19:12:05

[code]CREATE PROCEDURE sp_Update01212
(
@EmailAddy AS VARCHAR(150)
)
AS

SET NOCOUNT ON

BEGIN TRANSACTION

BEGIN TRY

UPDATE p
SET p.History = CASE
WHEN z.Field_Name = 'History' THEN z.History
ELSE p.History
END
p.PptUsr4_SS_SW = CASE
WHEN z.Field_Name = 'History' THEN 'N'
ELSE p.PptUsr4_SS_SW
END,
p.Attend_Code = CASE
WHEN z.Field_Name = 'Attend_Code' THEN z.Attend_Code
ELSE p.Attend_Code
END
p.PptUsr3_SS_SW = CASE
WHEN z.Field_Name = 'Attend_Code' THEN 'N'
ELSE p.PptUsr4_SS_SW
END,
p.Status = CASE
WHEN z.Field_Name = 'Status' THEN z.Status
ELSE p.Status
END,
p.PptUsr1_SS_SW = CASE
WHEN z.Field_Name = 'Status' AND z.Status >= 5 THEN 'N'
ELSE p.PptUsr1_SS_SW
END,
p.PptUsr2_SS_SW = CASE
WHEN z.Field_Name = 'Status' AND z.Status >= 5 THEN 'N'
ELSE p.PptUsr2_SS_SW
END,
p.UserField2 = CASE
WHEN z.Field_Name = 'UserField2' THEN z.UserField2
ELSE p.Status
END
FROM User01.TableP AS p
INNER JOIN User01.TableZ AS z ON z.Company = p.Company
AND z.Employee = p.Employee
AND z.TimeSeq = p.TimeSeq
AND z.Field_Name = p.Field_Name
WHERE z.Updated <> 'Y'

UPDATE User01.TableZ
SET Updated = 'Y'
WHERE Updated <> 'Y'

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

IF LEN(@EmailAddy) > 0
EXEC user05.sp_Mail 'NoReply@mycompany.com', @EmailAddy, 'Subject Test', 'Message Test'[/code]

E 12°55'05.25"
N 56°04'39.16"

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-26 : 12:23:32

Thanks you also Peso. I was thinking that I might just do a separate update like that for the updated flag. Would there be an issue though if someone was inserting a record into the table while it was going through the cases? Since the update at the end updates anything that is not a Y would it grab a record that may have just been added but missed the select?

Unfortunately this does not solve my other problem :( I am still not getting the update I need. I think the confusing is still in the join statement.

There is no p.FIELD_NAME so I can't do p.FIELD_NAME = z.FIELD_NAME

I am not sure if that is causing it to only update the first record in each set or not. I will be working on it during some free time today.

Again thanks to both of you and any other suggestions are greatly appreciated.

-Jessica

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-26 : 12:54:47

1) No, you have a TRANSACTION so your update will all go good or all go bad.
2) Just remove the "AND z.Field_Name = p.Field_Name" from the INNER JOIN.

E 12°55'05.25"
N 56°04'39.16"

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-26 : 16:05:51

Sorry I think I missed the point of what I was trying to say before. It is not that I thought it was being only half updated it is that the complete update is not updating correctly.

This is the testing data from the Z table. (6) records.


COMPANY   EMPLOYEE  TIME_SEQ    FIELD_NAME    STATUS     ATTEND_CODE  HISTORY  USERFIELD2   UPDATED
10	  1060	    2061266	ATTEND_CODE   0	         'L' 	      ''       ''	    ''      	 
10	  1060	    2061266	STATUS        7	  	 ''           ''       ''           ''
10	  1060	    2061266	USERFIELD2    0          ''	      ''       '8/23/2007'  '' 	
10	  1478	    2067218	STATUS        0	  	 'X'          ''       ''           ''	
10	  1478	    2067218	STATUS        6	  	 ''           ''       ''           '' 
10	  1478	    2067218	USERFIELD2    0	         ''           ''       '8/22/2007'  ''

In the p table after update should have
TIME_SEQ of 2061266 with ATTEND_CODE='L', STATUS=7 and USERFIELD2='8/23/2007'.
TIME_SEQ of 2067218 with ATTEND_CODE='X', STATUS=6 and USERFIELD2='8/22/2007'.

However it looks like the only two updates are to ATTEND_CODE. After the update completes the p table has
TIME_SEQ of 2061266 ATTEND_CODE='L', STATUS=0 and USERFIELD2='0'
TIME_SEQ of 2067218 ATTEND_CODE='X', STATUS=0 and USERFIELD2='0'

So everything seems to be working correctly but only the ATTEND_CODE is being updated. Since USERFILED2 is NULL before the update and then has a 0 after it I am assuming something is updating it but with the wrong data.

To verify that it is nothing to do with ATTEND_CODE I marked the two entries above as processed and ran it. It then updated the STATUS correctly but has '0' in for both USERFIELD2.

I think I know what it is.

In the P table TIME_SEQ in unique. In the Z table though it can have multiple listing because this is the table the updates are being stored in until they can be processed on the P table. So the INNER join is just matching the first COMPANY + EMPLOYEE + TIME_SEQ and updating that but skipping the other records. Sound like I am on to something :) maybe not.

Jessica

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-26 : 16:45:53

Post some sample data.

E 12°55'05.25"
N 56°04'39.16"

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-26 : 17:53:21

===Before update===


TABLE Z
COMPANY   EMPLOYEE  TIME_SEQ    FIELD_NAME    STATUS     ATTEND_CODE  HISTORY  USERFIELD2   UPDATED
10	  1060	    2061266	ATTEND_CODE   0	         'L' 	      ''       ''	    ''      	 
10	  1060	    2061266	STATUS        7	  	 ''           ''       ''           ''
10	  1060	    2061266	USERFIELD2    0          ''	      ''       '8/23/2007'  '' 	
10	  1478	    2067218	ATTEND_CODE   0	  	 'X'          ''       ''           ''	
10	  1478	    2067218	STATUS        6	  	 ''           ''       ''           '' 
10	  1478	    2067218	USERFIELD2    0	         ''           ''       '8/22/2007'  '' 

TABLE P
COMPANY   EMPLOYEE  TIME_SEQ    STATUS     ATTEND_CODE  HISTORY  USERFIELD2
10	  1060	    2061266	0	   '' 	        ''       ''	        	 
10	  1478	    2067218	0	   ''           ''       ''

===After the update this is what I am trying to get===


TABLE Z
COMPANY   EMPLOYEE  TIME_SEQ    FIELD_NAME    STATUS     ATTEND_CODE  HISTORY  USERFIELD2   UPDATED
10	  1060	    2061266	ATTEND_CODE   0	         'L' 	      ''       ''	    'Y'      	 
10	  1060	    2061266	STATUS        7	  	 ''           ''       ''           'Y'
10	  1060	    2061266	USERFIELD2    0          ''	      ''       '8/23/2007'  'Y' 	
10	  1478	    2067218	ATTEND_CODE   0	  	 'X'          ''       ''           'Y'	
10	  1478	    2067218	STATUS        6	  	 ''           ''       ''           'Y' 
10	  1478	    2067218	USERFIELD2    0	         ''           ''       '8/22/2007'  'Y' 

TABLE P
COMPANY   EMPLOYEE  TIME_SEQ    STATUS     ATTEND_CODE  HISTORY  USERFIELD2
10	  1060	    2061266	7	   'L' 	        ''       '8/23/2007'	        	 
10	  1478	    2067218	6	   'X'          ''       '8/22/2007'

===This is how it currently looks after the update===


TABLE Z
COMPANY   EMPLOYEE  TIME_SEQ    FIELD_NAME    STATUS     ATTEND_CODE  HISTORY  USERFIELD2   UPDATED
10	  1060	    2061266	ATTEND_CODE   0	         'L' 	      ''       ''	    'Y'      	 
10	  1060	    2061266	STATUS        7	  	 ''           ''       ''           'Y'
10	  1060	    2061266	USERFIELD2    0          ''	      ''       '8/23/2007'  'Y' 	
10	  1478	    2067218	ATTEND_CODE   0	  	 'X'          ''       ''           'Y'	
10	  1478	    2067218	STATUS        6	  	 ''           ''       ''           'Y' 
10	  1478	    2067218	USERFIELD2    0	         ''           ''       '8/22/2007'  'Y' 

TABLE P
COMPANY   EMPLOYEE  TIME_SEQ    STATUS     ATTEND_CODE  HISTORY  USERFIELD2
10	  1060	    2061266	0	   'L' 	        ''       '0'	        	 
10	  1478	    2067218	0	   'X'          ''       '0'

So in the example above it should be doing 3 updates to each of those records in the P table but it is only doing one.

Thank you,

-Jessica

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-27 : 04:40:46

[code]-- Prepare sample data
DECLARE @TableZ TABLE (COMPANY INT, EMPLOYEE INT, TIMESEQ INT, FIELDNAME SYSNAME, STATUS INT, ATTENDCODE CHAR(1), HISTORY CHAR(1), USERFIELD2 SYSNAME, UPDATED CHAR(1))

INSERT @TableZ
SELECT 10, 1060, 2061266, 'ATTEND_CODE', 0, 'L', 'p', '', '' UNION ALL
SELECT 10, 1060, 2061266, 'STATUS', 7, '', 'p', '', '' UNION ALL
SELECT 10, 1060, 2061266, 'USERFIELD2', 0, '', 'p', '8/23/2007', '' UNION ALL
SELECT 10, 1478, 2067218, 'ATTEND_CODE', 0, 'X', 'p', '', '' UNION ALL
SELECT 10, 1478, 2067218, 'STATUS', 6, '', 'p', '', '' UNION ALL
SELECT 10, 1478, 2067218, 'USERFIELD2', 0, '', 'p', '8/22/2007', ''

DECLARE @TableP TABLE (COMPANY INT, EMPLOYEE INT, TIMESEQ INT, STATUS INT, ATTENDCODE CHAR(1), HISTORY CHAR(1), USERFIELD2 SYSNAME)

INSERT @TableP
SELECT 10, 1060, 2061266, 0, '', '', '' UNION ALL
SELECT 10, 1478, 2067218, 0, '', '', ''

-- Do the updates
UPDATE p
SET --p.PptUsr1_SS_SW = COALESCE(z.PptUsr1_SS_SW, p.PptUsr1_SS_SW),
--p.PptUsr2_SS_SW = COALESCE(z.PptUsr2_SS_SW, p.PptUsr2_SS_SW),
--p.PptUsr3_SS_SW = COALESCE(z.PptUsr3_SS_SW, p.PptUsr3_SS_SW),
--p.PptUsr4_SS_SW = COALESCE(z.PptUsr4_SS_SW, p.PptUsr4_SS_SW),
p.AttendCode = COALESCE(z.AttendCode, p.AttendCode),
p.History = COALESCE(z.History, p.History),
p.Status = COALESCE(z.Status, p.Status),
p.UserField2 = COALESCE(z.UserField2, p.UserField2)
FROM @TableP AS p
INNER JOIN (
SELECT Company,
Employee,
TimeSeq,
MAX(CASE WHEN FieldName = 'Status' AND Status >= 5 THEN 'N' END) AS PptUsr1_SS_SW,
MAX(CASE WHEN FieldName = 'Status' AND Status >= 5 THEN 'N' END) AS PptUsr2_SS_SW,
MAX(CASE WHEN FieldName = 'Attend_Code' THEN 'N' END) AS PptUsr3_SS_SW,
MAX(CASE WHEN FieldName = 'History' THEN 'N' END) AS PptUsr4_SS_SW,
MAX(CASE WHEN FieldName = 'Attend_Code' THEN AttendCode END) AS AttendCode,
MAX(CASE WHEN FieldName = 'History' THEN History END) AS History,
MAX(CASE WHEN FieldName = 'Status' THEN Status END) AS Status,
MAX(CASE WHEN FieldName = 'UserField2' THEN UserField2 END) AS UserField2
FROM @TableZ
WHERE Updated <> 'Y'
GROUP BY Company,
Employee,
TimeSeq
) AS z ON z.Company = p.Company
AND z.Employee = p.Employee
AND z.TimeSeq = p.TimeSeq

UPDATE @TableZ
SET Updated = 'Y'
WHERE Updated <> 'Y'

-- Show updates values
SELECT * FROM @TableZ
SELECT * FROM @TableP[/code]

E 12°55'05.25"
N 56°04'39.16"

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-27 : 22:49:08

Wow... well from what I have tested it works great. Thank you so much!!! I have never even seen COALESCE before. I really don't understand the code but I am going to sit down and try and pick it apart.

Again thank you,

-Jessica

sqlhottie
Starting Member

9 Posts

Posted - 2007-08-28 : 00:05:22

Ok I have taken it apart and understand it now. Really really cool. Thank you so much for the lesson.

-Jessica

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources