0

I have a query which works fine in MySQL, 

update rpt_dev a inner join rpt_dev_temp b
on a.Interaction_UDL = b.Interaction_UDL
set a.Till_Next = datediff(b.Int_start,a.Int_start)
where a.Till_Next <= 0 and 
a.Int_start not in (select max(a.Int_start) from rpt_dev_temp a);

When I run it on Oracle I get the below errors:

1) SQL command not properly ended.

2) Missing set keyword

Utsav
  • 7,914
  • 2
  • 17
  • 38
Rajesh
  • 165
  • 1
  • 1
  • 10

1 Answers1

1

Update statements with join are bit tricky to implement in Oracle. Use MERGE instead

Also as mentioned in comments, datediff is not valid in Oracle. So subtracts dates directly. But b.Int_start - a.Int_start will give you difference in days. Multiply it by 24 or 24*60 or 24*60*60 to get difference in hours , minutes and seconds respectively.

merge into rpt_dev a
using rpt_dev_temp b
on (a.Interaction_UDL = b.Interaction_UDL)
when matched then update 
set a.Till_Next = (b.Int_start - a.Int_start)
where a.Till_Next <= 0 and 
a.Int_start not in (select max(Int_start) from rpt_dev_temp);

Note: Take backup of your original data before running. Test it on your test data and do no commit until you are sure you are updating correct rows.

In short, run update/delete statements with extra caution.

Utsav
  • 7,914
  • 2
  • 17
  • 38
  • Thank you let me check with the sample data. – Rajesh May 29 '17 at 15:55
  • As I said update is tricky to implement, hence use `merge` instead. In `merge` when you specify `when matched then update` , then it will on `update` on matching rows. – Utsav May 29 '17 at 15:57