update temp table with static values (no relation)

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 2008 Forums

Transact-SQL (2008)

update temp table with static values (no relation)

Author

Topic

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 00:03:47

i have made my query more simpl. now again i want to tell u the scenario
i have two tables shift_schedule_final, shift_schedule_Temp
from shift_schedule_Temp i am getting three fields time_in, time_out and shift_id that i wants to update in Shift_schedule_final Table. both the tables have same fields but data is only in shift_schedule_Temp the other one is empty.
i have inserted the date using loop in Shift_schedule_final now i want to update the time_in, time_out, shift_id fields in Shift_schedule_final but there is no link between these two tables. so i cannot use where clause. i am using the following subquery

update shift_schedule_final
set (shift_id,time_in, time_out) = (SELECT shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64)

it showing the message "Single row subquery return more the one rows.

following are the table fields

SHIFT_ID TIME_IN TIME_OUT
2.00 06:00:00 AM 02:00:00 PM
3.00 02:00:00 PM 10:00:00 PM
1.00 10:00:00 PM 06:00:00 AM
3.00 02:00:00 PM 10:00:00 PM
1.00 10:00:00 PM 06:00:00 AM
2.00 06:00:00 AM 02:00:00 PM
3.00 02:00:00 PM 10:00:00 PM
3.00 06:00:00 AM 02:00:00 PM
1.00 02:00:00 PM 10:00:00 PM
2.00 10:00:00 PM 06:00:00 AM

these values i want to update in shift_schedule_final

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 00:21:47

if shift_schedule_final has only these fields isnt a matter of insertion than updating?


INSERT INTO shift_schedule_final (shift_id,time_in, time_out) 
SELECT shift_id,time_in, time_ouT 
FROM shift_schedule_Temp WHERE ROWNUM <= 64

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 02:01:14

quote:
Originally posted by visakh16

if shift_schedule_final has only these fields isnt a matter of insertion than updating?

INSERT INTO shift_schedule_final (shift_id,time_in, time_out)
SELECT shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I don't want to insert because i have inserted the date through while loop now again i cannot insert the shiftid time in and out because it will be inserted on the next row of dates i just want to update the fields parallel to the date

following is the table where date is already inserted by while loop
declare
V_Counter DATE := '01-JUN-2011';
V_Counter1 DATE :='30-JUL-2011';
BEGIN
WHILE v_Counter <= v_Counter1 LOOP
INSERT INTO shift_schedule_final (date1) values (v_counter);
v_Counter := v_Counter + 1;

Following fields i want to fill which are blank parallel to the date.

SHIFT_ID DATE1 TIME_IN TIME_OUT SHIFT_ROTATE_ID
06/01/2011 00:00:00
06/02/2011 00:00:00
06/03/2011 00:00:00
06/04/2011 00:00:00
06/05/2011 00:00:00
06/06/2011 00:00:00
06/07/2011 00:00:00
06/08/2011 00:00:00
06/09/2011 00:00:00
06/10/2011 00:00:00
06/11/2011 00:00:00
06/12/2011 00:00:00
06/13/2011 00:00:00
06/14/2011 00:00:00
06/15/2011 00:00:00
06/16/2011 00:00:00
06/17/2011 00:00:00
06/18/2011 00:00:00
06/19/2011 00:00:00
06/20/2011 00:00:00
06/21/2011 00:00:00

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 02:05:38

I don't want to insert because i have inserted the date through while loop now again i cannot insert the shiftid time in and out because it will be inserted on the next row of dates i just want to update the fields parallel to the date

following is the table where date is already inserted by while loop
declare
V_Counter DATE := '01-JUN-2011';
V_Counter1 DATE :='30-JUL-2011';
BEGIN
WHILE v_Counter <= v_Counter1 LOOP
INSERT INTO shift_schedule_final (date1) values (v_counter);
v_Counter := v_Counter + 1;

Following fields i want to fill which are blank parallel to the date.

SHIFT_ID DATE1 TIME_IN TIME_OUT SHIFT_ROTATE_ID
06/01/2011 00:00:00
06/02/2011 00:00:00
06/03/2011 00:00:00
06/04/2011 00:00:00
06/05/2011 00:00:00
06/06/2011 00:00:00
06/07/2011 00:00:00
06/08/2011 00:00:00
06/09/2011 00:00:00
06/10/2011 00:00:00
06/11/2011 00:00:00
06/12/2011 00:00:00
06/13/2011 00:00:00
06/14/2011 00:00:00
06/15/2011 00:00:00
06/16/2011 00:00:00
06/17/2011 00:00:00
06/18/2011 00:00:00
06/19/2011 00:00:00
06/20/2011 00:00:00
06/21/2011 00:00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 02:05:44

ok thats fine. but on what order you want to update them to table ie which shift details in your sample data should go to record with which date value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 02:18:42

quote:
Originally posted by visakh16

ok thats fine. but on what order you want to update them to table ie which shift details in your sample data should go to record with which date value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
i want to order by Rotate_line_id wise see the following data

SHIFTID ROTATE_LINE_ID TIME_IN TIME_OUT
2.00 1.00 06:00:00 AM 02:00:00 PM
3.00 2.00 02:00:00 PM 10:00:00 PM
1.00 3.00 10:00:00 PM 06:00:00 AM
3.00 4.00 02:00:00 PM 10:00:00 PM
1.00 5.00 10:00:00 PM 06:00:00 AM
3.00 7.00 02:00:00 PM 10:00:00 PM
3.00 8.00 06:00:00 AM 02:00:00 PM
1.00 9.00 02:00:00 PM 10:00:00 PM
2.00 10.00 10:00:00 PM 06:00:00 AM
1.00 11.00 02:00:00 PM 10:00:00 PM
2.00 12.00 10:00:00 PM 06:00:00 AM
3.00 13.00 06:00:00 AM 02:00:00 PM
1.00 14.00 02:00:00 PM 10:00:00 PM
3.00 15.00 06:00:00 AM 02:00:00 PM
1.00 16.00 02:00:00 PM 10:00:00 PM
2.00 17.00 10:00:00 PM 06:00:00 AM
3.00 18.00 06:00:00 AM 02:00:00 PM
2.00 19.00 10:00:00 PM 06:00:00 AM
3.00 20.00 06:00:00 AM 02:00:00 PM
1.00 21.00 02:00:00 PM 10:00:00 PM
3.00 22.00 06:00:00 AM 02:00:00 PM
1.00 23.00 02:00:00 PM 10:00:00 PM
2.00 24.00 10:00:00 PM 06:00:00 AM
1.00 25.00 06:00:00 AM 02:00:00 PM
3.00 26.00 10:00:00 PM 06:00:00 AM
2.00 27.00 02:00:00 PM 10:00:00 PM
3.00 28.00 10:00:00 PM 06:00:00 AM
2.00 29.00 02:00:00 PM 10:00:00 PM
2.00 30.00 02:00:00 PM 10:00:00 PM
3.00 31.00 10:00:00 PM 06:00:00 AM
1.00 32.00 06:00:00 AM 02:00:00 PM
1.00 33.00 06:00:00 AM 02:00:00 PM
3.00 34.00 10:00:00 PM 06:00:00 AM
2.00 36.00 02:00:00 PM 10:00:00 PM
3.00 37.00 02:00:00 PM 10:00:00 PM
2.00 38.00 06:00:00 AM 02:00:00 PM
3.00 39.00 02:00:00 PM 10:00:00 PM
2.00 40.00 06:00:00 AM 02:00:00 PM
1.00 41.00 10:00:00 PM 06:00:00 AM
3.00 42.00 02:00:00 PM 10:00:00 PM
1.00 43.00 10:00:00 PM 06:00:00 AM
1.00 44.00 10:00:00 PM 06:00:00 AM
3.00 45.00 02:00:00 PM 10:00:00 PM
1.00 46.00 10:00:00 PM 06:00:00 AM
2.00 47.00 06:00:00 AM 02:00:00 PM
3.00 48.00 06:00:00 AM 02:00:00 PM
2.00 49.00 10:00:00 PM 08:00:00 PM
3.00 50.00 06:00:00 AM 02:00:00 PM
1.00 51.00 02:00:00 PM 10:00:00 PM
1.00 52.00 02:00:00 PM 10:00:00 PM
3.00 53.00 06:00:00 AM 02:00:00 PM
1.00 54.00 02:00:00 PM 10:00:00 PM
3.00 55.00 06:00:00 AM 02:00:00 PM
1.00 56.00 02:00:00 PM 10:00:00 PM
2.00 57.00 10:00:00 PM 06:00:00 AM
1.00 58.00 02:00:00 PM 10:00:00 PM
2.00 59.00 10:00:00 PM 06:00:00 AM
1.00 61.00 02:00:00 PM 10:00:00 PM
1.00 62.00 06:00:00 AM 02:00:00 PM
2.00 63.00 02:00:00 PM 10:00:00 PM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 02:37:55

sounds like this


update f
set f.shift_id=t.shift_id,
f.time_in=t.time_in, 
f.time_out=t.time_out
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE1) AS Seq,shift_id,time_in, time_ouT 
FROM shift_schedule_final) f
JOIN(
SELECT ROW_NUMBER() OVER (ORDER BY ROTATE_LINE_ID ) AS Seq,shift_id,time_in, time_ouT 
FROM shift_schedule_Temp WHERE ROWNUM <= 64)
)t
ON t.Seq= f.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 03:04:14

quote:
Originally posted by visakh16

sounds like this

update f
set f.shift_id=t.shift_id,
f.time_in=t.time_in,
f.time_out=t.time_out
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE1) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_final) f
JOIN(
SELECT ROW_NUMBER() OVER (ORDER BY ROTATE_LINE_ID ) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64)
)t
ON t.Seq= f.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

after the row f.time_out = t.time_out on FROM Clause following message showing

Command is not properly ENDED.

update f
set f.shift_id=t.shift_id,
f.time_in=t.time_in,
f.time_out=t.time_out
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE1) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_final) f
JOIN(
SELECT ROW_NUMBER() OVER (ORDER BY ROTATE_LINE_ID ) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64)
)t
ON t.Seq= f.Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 03:58:12

which editor are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 04:00:32

quote:
Originally posted by visakh16

which editor are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
i am using TOAD for sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 04:03:50

[code]
update f
set f.shift_id=t.shift_id,
f.time_in=t.time_in,
f.time_out=t.time_out
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE1) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_final) f
JOIN(
SELECT ROW_NUMBER() OVER (ORDER BY ROTATE_LINE_ID ) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64
)t
ON t.Seq= f.Seq

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 04:13:14

quote:
Originally posted by visakh16

update f
set f.shift_id=t.shift_id,
f.time_in=t.time_in,
f.time_out=t.time_out
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE1) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_final) f
JOIN(
SELECT ROW_NUMBER() OVER (ORDER BY ROTATE_LINE_ID ) AS Seq,shift_id,time_in, time_ouT
FROM shift_schedule_Temp WHERE ROWNUM <= 64
)t
ON t.Seq= f.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

could i use this code in oracle sql using toad
may be due to syntax it is showing this message?

hissam78
Starting Member

14 Posts

Posted - 2011-08-29 : 04:47:22

THANX QUERY IS WORKING GREAT HELP DO ONE MORE THING

FOLLOWING IS THE CODE THAT I AM USING FOR INSERTING THE LOOP ITS WORKING HOW CAN I INSERT ONE DATE 3 TIMES THIS CODING IS EXECUTE ONCE.

declare
V_Counter DATE := '01-JUN-2011';
V_Counter1 DATE :='30-JUL-2011';
BEGIN
WHILE v_Counter <= v_Counter1 LOOP
INSERT INTO shift_schedule_final (date1) values (v_counter);
v_Counter := v_Counter + 1;
END LOOP;
END;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 07:29:54

[code]declare
V_Counter DATE := '01-JUN-2011';
V_Counter1 DATE :='30-JUL-2011';
BEGIN
WHILE v_Counter <= v_Counter1 LOOP
INSERT INTO shift_schedule_final (date1) values
SELECT v_counter
FROM (SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)t;
v_Counter := v_Counter + 1;
END LOOP;
END;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hissam78
Starting Member

14 Posts

Posted - 2011-08-30 : 00:17:05

quote:
Originally posted by visakh16

declare
V_Counter DATE := '01-JUN-2011';
V_Counter1 DATE :='30-JUL-2011';
BEGIN
WHILE v_Counter <= v_Counter1 LOOP
INSERT INTO shift_schedule_final (date1) values
SELECT v_counter
FROM (SELECT 1 AS ID UNION ALL SELECT 2 UNION ALL SELECT 3)t;
v_Counter := v_Counter + 1;
END LOOP;
END;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thanx visakh
problem solved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:34:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources