6 Replies - 908 Views - Last Post: 24 June 2016 - 01:48 PM

#1 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Insert if not exist/Update if exist, multiple updates to one record.

Posted 23 June 2016 - 06:08 AM

Hi,
I am receiving a list of updates to perform on an employee table. New records are inserted existing ones updated. I've found a number of ways of handling it, if I only get one update per employee. If I get multiple updates per employee I'm struggling to get it to work. The latest thing I've tried is MERGE INTO. See code below:
This works great if I have say two existing employees with updates and two new employees. If I have two seperate updates for the one employee then I run into trouble. Or if I get a new employee and then an update to that employee, it basically decides the employee doesn't exist so tries to INSERT both occurrences. I've also tried IF EXIST in various forms.
MERGE INTO EeAbsence_Pattern WITH (HOLDLOCK) AS target
USING
(SELECT ed.EeID,ae.WorkingPatternStartDate,'1',ae.Sunday,ae.Monday,ae.Tuesday,ae.Wednesday,
        ae.Thursday,ae.Friday,ae.Saturday
 FROM unity_absences_export AS ae INNER JOIN EeDetails AS ed ON ed.EeRef = ae.EmployeeID WHERE ae.Sunday IS NOT NULL)
 AS source(EeID,WorkingPatternStartDate,WeekNo,Sun,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
ON
 (target.EeID = source.EeID)
  WHEN MATCHED THEN UPDATE SET DateFrom = CASE source.WorkingPatternStartDate WHEN ' ' THEN NULL ELSE
                                             (CASE substring(source.WorkingPatternStartDate,5,1) WHEN '-' THEN convert(datetime,source.WorkingPatternStartDate,111)
								              ELSE convert(datetime,source.WorkingPatternStartDate,103) END) END,
							   WeekNo=source.WeekNo,
							   Sunday=source.Sun,
							   Monday=source.Monday,
							   Tuesday=source.Tuesday,
							   Wednesday=source.Wednesday,
							   Thursday=source.Thursday,
							   Friday=source.Friday,
							   Saturday=source.Saturday
  WHEN NOT MATCHED THEN INSERT (EeID,DateFrom,WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
  VALUES (source.EeID,
          CASE source.WorkingPatternStartDate WHEN ' ' THEN NULL ELSE
                                             (CASE substring(source.WorkingPatternStartDate,5,1) WHEN '-' THEN convert(datetime,source.WorkingPatternStartDate,111)
								              ELSE convert(datetime,source.WorkingPatternStartDate,103) END) END,
		  source.WeekNo,
		  source.Sun,
		  source.Monday,
		  source.Tuesday,
		  source.Wednesday,
		  source.Thursday,
		  source.Friday,
		  source.Saturday);



I hope someone can help.
Regards,
Jim.

Is This A Good Question/Topic? 0
  • +

Replies To: Insert if not exist/Update if exist, multiple updates to one record.

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13569
  • View blog
  • Posts: 54,143
  • Joined: 12-June 08

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 23 June 2016 - 01:20 PM

I am unclear of the multiple update issue. Are the updates conflicting in some way?
Was This Post Helpful? 0
  • +
  • -

#3 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 24 June 2016 - 04:48 AM

Hi,
The error I get is :

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

It doesn't like the fact that I'm updating the same record twice.

The only reason I've looked into the MERGE INTO option is because of a problem I was having with an IF EXIST piece of code. Which ironically was fine when doing the UPDATE but struggled with new records. See code below:
IF EXISTS (SELECT EeID FROM EeAbsence_Pattern)
	BEGIN
		UPDATE EeAbsence_Pattern
		SET DateFrom = CASE ae.WorkingPatternStartDate WHEN ' ' THEN NULL ELSE
													 (CASE substring(ae.WorkingPatternStartDate,5,1) WHEN '-' THEN convert(datetime,ae.WorkingPatternStartDate,111)
													  ELSE convert(datetime,ae.WorkingPatternStartDate,103) END) END,
									   WeekNo='1',
									   Sunday=ae.Sunday,
									   Monday=ae.Monday,
									   Tuesday=ae.Tuesday,
									   Wednesday=ae.Wednesday,
									   Thursday=ae.Thursday,
									   Friday=ae.Friday,
									   Saturday=ae.Saturday
		FROM unity_absences_export AS ae INNER JOIN EeDetails AS ed ON ed.EeRef = ae.EmployeeID
	END
ELSE
	BEGIN
		INSERT INTO EeAbsence_Pattern (EeID,DateFrom,WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
		 SELECT ed.EeID,
          CASE ae.WorkingPatternStartDate WHEN ' ' THEN NULL ELSE
                                             (CASE substring(ae.WorkingPatternStartDate,5,1) WHEN '-' THEN convert(datetime,ae.WorkingPatternStartDate,111)
								              ELSE convert(datetime,ae.WorkingPatternStartDate,103) END) END,
		  '1',
		  ae.Sunday,
		  ae.Monday,
		  ae.Tuesday,
		  ae.Wednesday,
		  ae.Thursday,
		  ae.Friday,
		  ae.Saturday
		 FROM unity_absences_export AS ae INNER JOIN EeDetails AS ed ON ed.EeRef = ae.EmployeeID
	END



The above code is fine if the employee already exists and I can update the same record multiple times. It's also fine if the employee doesn't exist but only if the employee ID only appears once in the update batch.
If the employee ID appears twice then the query recognises that he needs to be inserted but it tries to insert both occurences and will get an error on the second insert because now the employee does exist.

I'm prepared to take a different tack! If I put a GROUP BY EeID clause in my SELECT statement I will only ever get one record per employee and I can get a time stamp onto the record so I can select the lastest record. So sometime like:

SELECT EeID,MAX(TimeStamp),Sunday,Monday, etc GROUP BY EeID



But I need some sort of aggregate for Sunday,Monday, etc and that's where I'm struggling. I want the value of Sunday,Monday, etc which is on the latest record.

Regards,
Jim.
Was This Post Helpful? 0
  • +
  • -

#4 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 24 June 2016 - 05:04 AM

Hi,
I'm having a look at OVER PARTITION BY and this produces a column called MaxTS which if I could put it into the WHERE clause would do my job.

SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest



This doesn't work :

SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp=MaxTS



And neither does this :

SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp=MAX(TimeStamp) OVER (PARTITION BY EeID)



Or this :

SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp=(SELECT MAX(TimeStamp) OVER (PARTITION BY EeID) FROM TimeStampTest) 



If I could get a list of records where the TimeStamp was equal to MaxTS I would have the correct list.

Regards,
Jim.
Was This Post Helpful? 0
  • +
  • -

#5 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 24 June 2016 - 06:55 AM

Hi,
I have a table with the following data in it:

EeID,Sunday,TimeStamp
1,False,1
1,True,4
2,False,2
2,True,3

I want to produce a list of records with one record per employee with the details for the latest TimeStamp. i.e.

EeID,Sunday
1,True
2,True

If I run this :
SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest


I get :
EeID,Sunday,TimeStamp,MaxTS
1,False,1,4
1,True,4,4
2,False,2,3
2,True,3,3

So I want the records where TimeStamp=MaxTS.

I've tried :
SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp=MaxTS


I get Invalid column name MaxTS

I've tried :
SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp=MAX(TimeStamp) OVER (PARTITION BY EeID) 


But that's not allowed either

What I need for the above is :
SELECT EeID,Sunday,TimeStamp,MAX(TimeStamp) OVER (PARTITION BY EeID) MaxTS
FROM TimeStampTest
WHERE TimeStamp='3' OR TimeStamp='4'


But I'll never know the value of the TimeStamps I'm looking for.

I hope this makes sense.
Regards,
Jim.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13569
  • View blog
  • Posts: 54,143
  • Joined: 12-June 08

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 24 June 2016 - 07:07 AM

Why wouldn't you use 'group by' and then join off the results?

Example:
crea te table #foo (lVal int, sVal varchar(20), DTENTERED datetime)

inse rt into #foo(lVal, sVal, DTENTERED) values (1, 'one', '06/01/2016')
inse rt into #foo(lVal, sVal, DTENTERED) values (1, 'one111', '05/01/2016')
inse rt into #foo(lVal, sVal, DTENTERED) values (2, 'two', '06/04/2016')
inse rt into #foo(lVal, sVal, DTENTERED) values (2, 'two222', '06/01/2016')

select *
from #foo

-- group by the key
-- so you can use the aggregate 'max' on the date time
-- by default you need to use 'max' on the other column name but that shouldn't affect anything.

select max(DTENTERED),  max(lVal )
from #foo
group by lVal

-- you would join off this subquery to get the rest of the data from the table.

select a.*
from #foo a
join (select max(lVal ) as lval, max(DTENTERED) as DTENTERED
      from #foo
      group by lVal
) b on a.lVal = b.lVal and a.DTENTERED = b.DTENTERED

dr op table #foo 



lVal        sVal                 DTENTERED
----------- -------------------- -----------------------
1           one                  2016-06-01 00:00:00.000
1           one111               2016-05-01 00:00:00.000
2           two                  2016-06-04 00:00:00.000
2           two222               2016-06-01 00:00:00.000

(4 row(s) affected)

                        
----------------------- -----------
2016-06-01 00:00:00.000 1
2016-06-04 00:00:00.000 2

lVal        sVal                 DTENTERED
----------- -------------------- -----------------------
1           one                  2016-06-01 00:00:00.000
2           two                  2016-06-04 00:00:00.000




Also, please do not create duplicate topics. Merging.
Was This Post Helpful? 1
  • +
  • -

#7 jcborland  Icon User is offline

  • D.I.C Head

Reputation: -2
  • View blog
  • Posts: 112
  • Joined: 16-December 13

Re: Insert if not exist/Update if exist, multiple updates to one record.

Posted 24 June 2016 - 01:48 PM

Hi,
Thanks that works great.
--
-- Update existing records
--
PRINT 'Updating existing records - Start.'
UPDATE EeAbsence_Pattern
SET EeID=ed.EeID,
	DateFrom=a.WorkingPatternStartDate,
	WeekNo='1',
	Sunday=a.Sunday,
	Monday=a.Monday,
	Tuesday=a.Tuesday,
	Wednesday=a.Wednesday,
	Thursday=a.Thursday,
	Friday=a.Friday,
	Saturday=a.Saturday
	
	FROM unity_absences_export a
	INNER JOIN (SELECT MAX(EmployeeID ) AS EmployeeID, MAX(WorkingPatternStartDate) AS WorkingPatternStartDate
				FROM unity_absences_export
				GROUP BY EmployeeID)
				b ON a.EmployeeID = b.EmployeeID and a.WorkingPatternStartDate = b.WorkingPatternStartDate
	INNER JOIN EeDetails ed ON ed.EeRef = a.EmployeeID
	LEFT JOIN EeAbsence_Pattern eap ON eap.EeID = ed.EeID
	WHERE eap.EeID IS NOT NULL
PRINT CHAR(13) + 'Updating existing records - End.'
--
-- Insert new records
--
PRINT CHAR(13) + 'Inserting new records - Start.' 

INSERT INTO EeAbsence_Pattern (EeID,DateFrom,WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

	SELECT ed.EeID,a.WorkingPatternStartDate,'1',a.Sunday,a.Monday,a.Tuesday,a.Wednesday,a.Thursday,a.Friday,a.Saturday
	FROM unity_absences_export a
	INNER JOIN (SELECT MAX(EmployeeID ) AS EmployeeID, MAX(WorkingPatternStartDate) AS WorkingPatternStartDate
				FROM unity_absences_export
				GROUP BY EmployeeID)
				b on a.EmployeeID = b.EmployeeID and a.WorkingPatternStartDate = b.WorkingPatternStartDate
	inner join EeDetails ed on ed.EeRef = a.EmployeeID
	left join EeAbsence_Pattern eap on eap.EeID = ed.EeID
	where eap.EeID IS NULL
PRINT CHAR(13) + 'Inserting new records - End.'



I don't actually have a unique time stamp( or unique anything ) but I can add one. I was initially trying to emulate the LAST() aggregate function that's available in MS Access.
I ended up with this which also worked but it was a little complex.
	--
	-- Update existing records
	--
	BEGIN
			
			WITH AbsencePatternAlias
			AS
			(
			SELECT Row_Number() OVER(PARTITION BY EmployeeID ORDER BY AbsenceCode DESC) RowNo,EmployeeID,WorkingPatternStartDate,
																									'1' AS WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
			From unity_absences_export
			)

			UPDATE EeAbsence_Pattern

			SET
			 EeID=ed.EeID,
			 DateFrom=apa.WorkingPatternStartDate,
			 WeekNo='1',
			 Sunday=apa.Sunday,
			 Monday=apa.Monday,
			 Tuesday=apa.Tuesday,
			 Wednesday=apa.Wednesday,
			 Thursday=apa.Thursday,
			 Friday=apa.Friday,
			 Saturday=apa.Saturday
			FROM AbsencePatternAlias AS apa 
			INNER JOIN EeDetails AS ed ON ed.EeRef=apa.EmployeeID
			LEFT JOIN EeAbsence_Pattern AS eap ON ed.EeID = eap.EeID
			WHERE RowNo = 1 AND eap.EeID IS NOT NULL;
			PRINT 'Updated existing records'
	END
	--
	-- Insert new records
	--
	BEGIN
			
			WITH AbsencePatternAlias
			AS
			(
			SELECT Row_Number() OVER(PARTITION BY EmployeeID ORDER BY AbsenceCode DESC) RowNo,EmployeeID,WorkingPatternStartDate,
																									'1' AS WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
			From unity_absences_export
			)

			INSERT INTO EeAbsence_Pattern (EeID,DateFrom,WeekNo,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

			SELECT ed.EeID,WorkingPatternStartDate,'1' AS WeekNo,apa.Sunday,apa.Monday,apa.Tuesday,apa.Wednesday,apa.Thursday,apa.Friday,apa.Saturday
			FROM AbsencePatternAlias AS apa 
			INNER JOIN EeDetails AS ed ON ed.EeRef=apa.EmployeeID
			LEFT JOIN EeAbsence_Pattern AS eap ON ed.EeID = eap.EeID
			WHERE RowNo = 1 AND eap.EeID IS NULL;
			PRINT 'Inserted new records'

	END



Thanks again for all the help.
Regards,
Jim.


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1