Click here to Skip to main content
15,792,430 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Dear All I have two tables in Database first is the following :
EmployeeID   01/04/2017   02/04/2017    03/04/2017   04/04/2017
1             1-9,6

second table is
EmployeeID    DayDate       SCHEDULEID    SHIFT 
1            01/04/2017        1             9,6
1            02/04/2017        1             5
1            03/04/2017        30            7,6
1            04/04/2017        30            9
2            01/04/2017        30            9
2            02/04/2017        6             3
3            01/04/2017        6             7,6
3            03/04/2017        6             9

i want make a stored procedure or SQL statement to update the first table data from the second as to set the data like for employeeid =1 in the date 01/04/2017 is
SCHEDULEID-SHIFT which is 1-9,6

What I have tried:

I have tried to do it with cursor and loop through first table and select the value of the shift from second table then update the column data but it doesn't work properly
so can get help in this !!
Updated 9-May-17 10:00am
CHill60 9-May-17 13:46pm    
There is rarely any need at all to use a loop in T-SQL!
Mohamed Hashem 9-May-17 13:49pm    
can i get the data of first table by native sql ??!
CHill60 9-May-17 13:52pm    
You can pivot the second table to get the first ... I'm just trying to sort out an example but this link is what I mean - Oracle / PLSQL: PIVOT Clause[^]
Mohamed Hashem 9-May-17 14:15pm    
I have do an update to the question that will clarify why not to use Pivot

1 solution

Ok, first up - my apologies, the following solution is in T-SQL for SQL Server. SQLFiddle is not working for me just now and I unistalled Oracle a few weeks ago.

This is the principle that I mean, use the link I gave in my comment above to adjust this code to work on Oracle (if it doesn't already)
select EmployeeID, ISNULL([01/04/2017],''),ISNULL([02/04/2017],''),ISNULL([03/04/2017],''),ISNULL([04/04/2017],'')
	select EmployeeID, DayDate, [Shift]
	from table2
) AS Q
	Max([Shift]) for DayDate IN ([01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
) AS pvt

Results are
1	9	5	7,6	9
2	9	3		
3	7,6		9	

As an aside, that is a very bad database design. You should never separate items in a column with commas - use another table and link it, or allow for multiple entries per Employee per Date (as long as the shift number is different)

[EDIT - OP has changed the requirements slightly]
This will probably still need some adjustment to work in Oracle
INSERT INTO table1 (EmployeeID, [01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
	select EmployeeID, ISNULL([01/04/2017],''),ISNULL([02/04/2017],''),ISNULL([03/04/2017],''),ISNULL([04/04/2017],'')
		select EmployeeID, DayDate, RES = TO_CHAR([SCHEDULEID]) + '-'+[Shift]
		from table2
		Max(RES) for DayDate IN ([01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
Share this answer
Mohamed Hashem 9-May-17 14:25pm    
thanks, I know but it this design already exists.
Maciej Los 9-May-17 14:41pm    
Mohamed Hashem 10-May-17 4:19am    
Thanks, But it doesn't work
CHill60 10-May-17 4:34am    
What happens? Do you get any results or do you get an error? If it's an error did you change the query to match Oracle syntax as per the link?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900