Start Free Trial

asked on

merge sql in oracle

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

slightwv (䄆 Netminder)

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?

ASKER

Yes I have a test system that I can test

slightwv (䄆 Netminder)

OK then, make the changes and let me know what happens?

ASKER

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

slightwv (䄆 Netminder)

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

ASKER

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

slightwv (䄆 Netminder)

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

ASKER

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

ASKER

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

slightwv (䄆 Netminder)

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.

slightwv (䄆 Netminder)

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

ASKER

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit

Running this in toad on test datadase

ASKER

Ithink its oracle version conflict. I got the same error running your example

slightwv (䄆 Netminder)

Can you try from sqlplus?

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

ASKER

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

slightwv (䄆 Netminder)

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.

ASKER

OK I will make it simple

slightwv (䄆 Netminder)

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

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

When I compile this I get error at
set start_time = start_time1,

unable to resolve start time as a column

slightwv (䄆 Netminder)

>>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')

ASKER

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

slightwv (䄆 Netminder)

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

ASKER

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

ASKER

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

slightwv (䄆 Netminder)

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

ASKER

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

thanks