Solved

merge sql in oracle

Posted on 2011-09-07
31
534 Views
Last Modified: 2012-05-12
merge into dept_staff a
using (
select   TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2),':')) as start_time,
         TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
         -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as time_worked
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
  and e.payroll_id = ds.payroll_id
--  and ds.payroll_id = 'ZZW0023516'
  and ds.schedule_date = '02-sep-2011'
  and ds.site_code = 'N208') b
  ON (a.payroll_id = b.payroll_id)
  WHEN MATCHED THEN
  update dept_staff
  set (a.start_time,a.end_time,a.total_hours)=
  (b.start_time,b.end_time,b.time_worked)

-----------------------------------------

NAME      PAYROLL_ID           START_TIME    END_TIME    TIME_WORKED
Alanis,Diana      ZZW0024518                  
Arlen,Melissa L      ZZW0000218                  
Bailey,Gwendolyn L      ZZW0002000                  
Bird,Kelli                      ZZW0023018                  
Brown,Lisa      ZZW0023811                  
Conant,James C      ZZW0023569                  
Cutter,Susan M      ZZW0001221                  
Czerak,Mary F      ZZW0023711                  
Daggett,Kathy J      ZZW0000258                  
Danahy,Kimberly J      ZZW0023843                  
Doepp,Gail      ZZW0001280                  
Dorneden,Mary D      ZZW0024096                  
Dunn,Kathleen F      ZZW0004003

I want to update for   Arlen,Melissa L      ZZW0000218
                                   Czerak,Mary F      ZZW0023711                  
0
Comment
Question by:anumoses
  • 17
  • 14
31 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36497502
Look at the MERGE syntax on:
http://psoug.org/reference/merge.html

You do not provide the FULL update syntax.
I have not verified the order of the columns with the table aliases.  I'm just providing basic syntax.

...
WHEN MATCHED THEN
  set a.start_time=b.start_time, a.end_time=b.end_time, a.total_hours=b.time_worked
...


Do you not have a test system where you can run this code to verify syntax and correctness?

0
 
LVL 6

Author Comment

by:anumoses
ID: 36497506
Yes I have a test system that I can test
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36497515
OK then, make the changes and let me know what happens?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36497532
This is what I have. Please check this and let me know

merge into dept_staff a
using (
select   TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2),':')) as start_time,
         TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
         -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as time_worked
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
  and e.payroll_id = ds.payroll_id
--  and ds.payroll_id = 'ZZW0023516'
  and ds.schedule_date = '02-sep-2011'
  and ds.site_code = 'N208') b
  ON (a.payroll_id = b.payroll_id)
   WHEN MATCHED THEN
UPDTAE
    set a.start_time=b.start_time,
          a.end_time=b.end_time,
          a.total_hours=b.time_worked
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36497548
>>Please check this and let me know

What do you expect me to check?

I cannot check anything other than a visually scan for syntax.  I will likely miss obscure syntax errors.  I cannot run anything since I don't have your setup.

This is why I keep asking about your test system.  Just run the code.  It will tell you it it is correct and the data will tell you if it worked the way you need it to work.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36497691
START_TIME|END_TIME|TIME_WORKED
800             |430          |850

These are the values I want to update on the employee with payroll id
'ZZW0023516'

Instead of ds.start_time.......
I want to have  those values hard coded for testing.
Can you guide me in that ? I tried replacing line 0800, 0430 I am getting error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36497718
>>I want to have  those values

I basically understand what you are wanting to update.  

Again, since I don't have your tables and test data I can only offer basic advice.

The syntax 'looks' good to me.  Nothing is jumping out at me that is wrong from a syntax issue.

>>I am getting error
 
What is the error?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36497918
merge into dept_staff a
using (
select
 TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(800, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(800, 'FM0000'),3,2),':')) as start_time,
        TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(430, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(430, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(430,'0000')||'P' || 'M' ,'HHMIAM')
         -to_date(to_char(830,'0000')||'A' || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as time_worked
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
  and e.payroll_id = ds.payroll_id
  and ds.payroll_id = 'ZZW0023684'--'ZZW0000218'
  and ds.schedule_date = '02-sep-2011'
  and ds.site_code = 'N208') b
  ON (a.payroll_id = b.payroll_id)
   WHEN MATCHED THEN
   UPDATE
    set a.start_time=b.start_time,
        a.end_time=b.end_time,
          a.total_hours=b.time_worked
------------------------------
ORA-00905 - missing keyword error
0
 
LVL 6

Author Comment

by:anumoses
ID: 36498053
I tried this

create table test_dept_staff
(payroll_id varchar2(15),
start_time number(9),
end_time number (9),
total_hours number(9))

insert into test_dept_staff (payroll_id) values('ZZW0024518');
insert into test_dept_staff (payroll_id) values('ZZW0000218');
insert into test_dept_staff (payroll_id) values('ZZW0002000');
insert into test_dept_staff (payroll_id) values('ZZW0023018');
insert into test_dept_staff (payroll_id) values('ZZW0023811');
insert into test_dept_staff (payroll_id) values('ZZW0023569');
insert into test_dept_staff (payroll_id) values('ZZW0001221');
insert into test_dept_staff (payroll_id) values('ZZW0000258');
insert into test_dept_staff (payroll_id) values('ZZW0023843');


commit

Then run the query

merge into test_dept_staff a
using (
select   TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2),':')) as start_time,
         TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
         -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as total_hours
from test_dept_staff ds
where ds.payroll_id = 'ZZW0000218' )b
  ON (a.payroll_id = b.payroll_id)
   WHEN MATCHED THEN
   UPDATE
    set a.start_time=b.start_time,
        a.end_time=b.end_time,
          a.total_hours=b.total_hours
-----------------------

ORA-00905 - missing keyword error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498128
I just realized from your other question:  You went with REPLACE to get rid of the ':' that you actually are purposely putting in.

You do realize how over complicated you made all this.

You take a value, use SUBSTR to split it into two pieces to add a ':' in the middle then turn around and remove the ':'.

Anyway, back to the question.

>>I tried this

The columns don't match.  you have start_AMPM in the sample table start_TIME.

After I fix that, I get a syntax error on the || 'M' concatination.

Even after I fix that, I do not get the ORA-00905 - missing keyword error.

What are you using to execute this and what version of Oracle are you running.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498136
Here is a very simple example of merge.

See if this runs for you.  I tested it using 10.2.0.3.
drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 char(1), col2 char(1));
create table tab2(col1 char(1), col2 char(1));

insert into tab1 values('a','1');
insert into tab1 values('b','2');

insert into tab2 values('a',null);
insert into tab2 values('c',null);


MERGE INTO tab2 t2
    USING (SELECT col1, col2 from tab1) t1
    ON (t1.col1 = t2.col1)
    WHEN MATCHED THEN
        UPDATE SET t2.col2=t1.col2
;

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36498142
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit

Running  this in toad on test datadase
0
 
LVL 6

Author Comment

by:anumoses
ID: 36498168
Ithink its oracle version conflict. I got the same error running your example
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498181
Can you try from sqlplus?

I would like to know if Toad or Oracle 9i has the syntax problem.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36498226
SQL> ed
Wrote file afiedt.buf

  1  MERGE INTO tab2 t2
  2      USING (SELECT col1, col2 from tab1) t1
  3      ON (t1.col1 = t2.col1)
  4      WHEN MATCHED THEN
  5*         UPDATE SET t2.col2=t1.col2
  6  /
        UPDATE SET t2.col2=t1.col2
                                 *
ERROR at line 5:
ORA-00905: missing keyword

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498228
I found the problem.

MERGE has a WHEN NOT MATCHED syntax that is required in 9i and not in 10g and above.
Maybe MERGE was a bad choice.  Let me rethink this.

In the mean time, rethink all the SUBSTR and REPLACE calls that you have put together over the last several questions.

I really don't think you need them.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36498234
OK I will make it simple
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36498316
Do you have Primary Keys set up?

See if this gets you what you need.  It should run in 9i.
drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 char(1) primary key, col2 char(1));
create table tab2(col1 char(1) primary key, col2 char(1));

insert into tab1 values('a','1');
insert into tab1 values('b','2');

insert into tab2 values('a',null);
insert into tab2 values('c',null);


update ( select t1.col1 old_col1, t1.col2 old_col2, t2.col1 new_col1, t2.col2 new_col2
           from tab1 t1, tab2 t2
          where t1.col1=t2.col1
            )
  set new_col2 = old_col2;

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 36498472
payroll_id  is the primary key. This query worked and it updated.
But you are taking values from the table. I have to update the table based on the values the user inputs
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36498862
OK, then just replace my old_col2 with your hard-coded values?

Notice below, I just hard-coded the 'Z'.  You will just add your, hopefully heavily updated, date/time values.
-------------------------
update ( select t1.col1 old_col1, 'Z' old_col2, t2.col1 new_col1, t2.col2 new_col2
           from tab1 t1, tab2 t2
          where t1.col1=t2.col1
            )
  set new_col2 = old_col2;
0
 
LVL 6

Author Comment

by:anumoses
ID: 36502031
This is the sql I have.

update (
        select SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2) as start_time1,
               SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2) as end_time1,
               TO_NUMBER(REPLACE(to_char(((to_date(to_char(:dtl.end_time,'0000')||:dtl.end_ampm || 'M' ,'HHMIAM')
              -to_date(to_char(:dtl.start_time,'0000')||:dtl.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as total_hours1
          from department d,emp_unexcld e,dept_staff ds
         where d.department_id = e.department_id
           and e.payroll_id = ds.payroll_id
           and ds.schedule_date = :hdr.sched_date
           and ds.site_code = :hdr.site_code
      )
   set start_time = start_time1,
       end_time = end_time1,
       total_hours = total_hours1;
---------------------just wanted to know id this is ok before I update on my test database.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36502391
When I compile this I get error at
 set start_time = start_time1,

unable to resolve start time as a column
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502424
>>just wanted to know id this is ok before I update on my test database.

Again, I have no way of knowing.  That is what a test database is for:  Testing things to make sure they are OK.

>>unable to resolve start time as a column
I don't see a start_time column being selected in the select statement.  Just because it is in the table, it needs to be selected.


Also, what does this really do:
SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2)

looks like it is doing the same thing as:
TO_CHAR(ds.start_time, 'FM0000')
0
 
LVL 6

Author Comment

by:anumoses
ID: 36502497

---------------
update (
select   TO_CHAR(ds.start_time, 'FM0000') starttime,
            :dtl.start_time as start_time,
            TO_CHAR(ds.end_time, 'FM0000') endtime,
           :dtl.end_time as end_time
          from department d,emp_unexcld e,dept_staff ds
         where d.department_id = e.department_id
           and e.payroll_id = ds.payroll_id
           and ds.schedule_date = :hdr.sched_date
           and ds.site_code = :hdr.site_code
      )
   set starttime = start_time,
       endtime = end_time;

------------------- For testing I just took two columns. Is this the way to be coded?


           
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36502605
>>Is this the way to be coded?

You are not understanding what I'm trying to tell you:  I cannot answer this.

If you run it and it works, then it is the correct way.  If not, then it isn't.

Unless I have exact copies of your tables and data there is no way I can provide 100% correct SQL.

I can tell you it 'look's OK but I'm not a Forms person so I can only assume the bind variables are correct.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36503487
I have another question.
There is a column called sequential_number that is a sequence and is populated on update. So can I have that column defined?
select SEQUENTIAL_NUMBER_SEQ.nextval into :dtl.sequential_number from dual;

update (
        select ds.sequential_number as sequential_number,
               :dtl.sequential_number as sequential_number1,
               ds.start_time as start_time,
--.....................
from...
where.....
)
set sequential_number = :sequential_number1
       start_time = start_time1,
       end_time = end_time1,
       total_hours = total_hours1,
       start_hours = start_hours1,
       start_minutes = start_minutes1,
       end_hours = end_hours1,
       end_minutes = end_minutes1;
----------------------
compiles and when I run the form, it says sequential_number cannot be null. Its basically trying to update record that have not been changed.
I added data for user Arlen,Melissa L and on save the cursor moves down and this is the error. So is there a way to only update records that we make changes for? so we donot have that sequence problem which in the database is a NOT NULL column
screen-print.JPG
0
 
LVL 6

Author Comment

by:anumoses
ID: 36503493
from department d,emp_unexcld e,dept_staff ds
         where d.department_id = e.department_id
           and e.payroll_id = ds.payroll_id
           and ds.schedule_date = :hdr.sched_date
           and ds.site_code = :hdr.site_code
           and :dtl.start_time is not null

I changed the where clause but does not work
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36503519
>>So can I have that column defined?

Sounds like it should work but I'm not a Forms person.

>>I changed the where clause but does not work

Define 'not work'?  Errors, not updating what you think it should be updating, too many rows, not enough rows???
0
 
LVL 6

Author Comment

by:anumoses
ID: 36503547
As you see in the screen print attached, user Cutter Susan M,
I have not updated anything. The cursor is there, but no sequential_number, since I am populating sequential number only for the one I updated. But database is being updated in full , so it finds missing sequential_number.
too many rows ....
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 36503572
That error says it was on Insert not Update.  That is a different question.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36509788
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now