Stored procedure to update table data in oracle

Question

1.00/5 (1 vote)

See more:

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
2
3
4

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

Posted 9-May-17 7:44am

Mohamed Hashem

Updated 9-May-17 9:00am

v3

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2017-05-09T08:10:00

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)

SQL

select EmployeeID, ISNULL([01/04/2017],''),ISNULL([02/04/2017],''),ISNULL([03/04/2017],''),ISNULL([04/04/2017],'')
FROM
(
	select EmployeeID, DayDate, [Shift]
	from table2
) AS Q
PIVOT
(
	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

SQL

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],'')
	FROM
	(
		select EmployeeID, DayDate, RES = TO_CHAR([SCHEDULEID]) + '-'+[Shift]
		from table2
	) 
	PIVOT
	(
		Max(RES) for DayDate IN ([01/04/2017],[02/04/2017],[03/04/2017],[04/04/2017])
	) 
);