Home » SQL & PL/SQL » SQL & PL/SQL » How to write this query? (10g)
How to write this query? [message #341058] Fri, 15 August 2008 23:00 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

How would I approach to write this query?

I want to copy a subset of data FROM user_id = 1, file_id =20
date_time>='01/01/2008 00:00' and date_time <='01/01/2008 23:00'
TO user_id = 2 with the exact same data set as user_id =1.

thanks,


--sample table and data
create table a(
user_id number,
file_id number,
date_time date,
value number);

insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 00:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 01:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 02:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 03:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 04:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 05:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 06:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 07:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 08:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 09:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 10:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 11:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 12:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 13:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 14:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 15:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 16:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 17:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 18:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 19:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 20:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 21:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 22:00',100);
insert into a( user_id,file_id,date_time,value)
values(1,20,'01/01/2008 13:00',100);



Re: How to write this query? [message #341059 is a reply to message #341058] Fri, 15 August 2008 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

With Oracle characters between single quote marks are strings.
'this is a string 2008-08-15 21:03:00 and not a date'
use the TO_DATE() function when you need a date datatype.

Insert into TABLE_A select .......
                    from TABLE_A
                    where ...

[Updated on: Fri, 15 August 2008 23:06] by Moderator

Report message to a moderator

Re: How to write this query? [message #341060 is a reply to message #341059] Fri, 15 August 2008 23:24 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
I knew this part, but
how can I assign the new values to user_id 2?
Re: How to write this query? [message #341061 is a reply to message #341058] Fri, 15 August 2008 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
INSERT INTO TABLE_A SELECT 2,file_id,date_time,value 
                    FROM TABLE_A
                    WHERE .....

Re: How to write this query? [message #341062 is a reply to message #341061] Fri, 15 August 2008 23:36 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Thanks for your help!
Re: How to write this query? [message #341063 is a reply to message #341058] Fri, 15 August 2008 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please post your solution as stated in the Posting Guidelines which you repeatedly ignore otherwise in the future Your On Your Own (YOYO)!
Re: How to write this query? [message #341064 is a reply to message #341061] Fri, 15 August 2008 23:44 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Here's my solution great help from anacedent.

INSERT INTO TABLE_A SELECT 2,file_id,date_time,value
FROM TABLE_A WHERE user_id=1 and file_id =20 and
date_time >='01/01/2008 00:00' and date_time <='01/01/2008 23:00'
Re: How to write this query? [message #341065 is a reply to message #341058] Fri, 15 August 2008 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
'this is a string NOT date'! and is equivalent to '01/01/2008 23:00'

You need to use TO_DATE!
Re: How to write this query? [message #341069 is a reply to message #341065] Sat, 16 August 2008 00:41 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Revised:

INSERT INTO TABLE_A SELECT 2,file_id,date_time,value
FROM TABLE_A WHERE user_id=1 and file_id =20 and
date_time >= to_date('01/01/2008 00:00',MM/DD/YYYY HH24:MI') and date_time <= to_date('01/01/2008 23:00',MM/DD/YYYY HH24:MI')
Re: How to write this query? [message #341071 is a reply to message #341069] Sat, 16 August 2008 00:50 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Much better.

Another suggestion: instead of
date_time >= some_value and date_time <= another_value
you might use BETWEEN operator, as
date_time between some_value and another_value
Previous Topic: ANSI Join inline view with GROUP BY Bug?
Next Topic: Updating one table from other two tables
Goto Forum:
  


Current Time: Sun Dec 04 12:34:01 CST 2016

Total time taken to generate the page: 0.11704 seconds