Converting oracle sql script to mysql

Question

Hi I'm looking to convert part of my oracle sql script into mysql script.

The part that I'm converting is as follows:

create table inspection
(property_no number(5) not null references prop_for_rent(property_no),
staff_no number(5) not null references staff(staff_no),
inspect_date date,
comments varchar2(50),
primary key(property_no,staff_no,inspect_date))

I have started to convert just having difficulty with the primary key which throws an error #1062 - Duplicate entry for primary key when inserting into the db. image link below :

https://i.sstatic.net/VfVW9.png

If you change number to numeric and varchar2 to varchar it should work: sqlfiddle.com/#!9/fb695e — trincot
– trincot, Commented Dec 15, 2017 at 19:20
That error should occur when you insert data into the table. It should not occur when creating the table. — clinomaniac
– clinomaniac, Commented Dec 15, 2017 at 19:26
yes the error does show when I am inserting records : insert into inspection values(1003,201,'12-JUN-2012','Cracked sink in kitchen requires repair'); MySQL said: Documentation #1062 - Duplicate entry '1003-201-0000-00-00' for key 'PRIMARY — Colin Jane
– Colin Jane, Commented Dec 15, 2017 at 19:29
my inserts that I need:insert into inspection values(1001,201,'12-JUN-2010','No problems'); insert into inspection values(1002,201,'13-JUN-2010','Crockery needs to be replaced'); insert into inspection values(1003,201,'12-JUN-2009','Broken window pane requires urgent repair'); insert into inspection values(1006,203,'21-MAR-2010','No problems'); insert into inspection values(1007,203,'01-DEC-2010','Sofa trim pulled apart by cat. Repair needed'); insert into inspection values(1003,201,'12-JUN-2012','Cracked sink in kitchen requires repair'); — Colin Jane
– Colin Jane, Commented Dec 15, 2017 at 19:30

spencer7593 · Accepted Answer · 2017-12-15 19:43:50Z

1

I think you will find that the values of the inspect_date column that were inserted are zero dates '0000-00-00' and that's because the value provided in the INSERT statement '12-JUN-2010' is not a valid format for a date value in MySQL.

In MySQL, we could do this:

... VALUES(1001,201,STR_TO_DATE('12-JUN-2010','%d-%b-%Y'),'No  problems')
                    ^^^^^^^^^^^^             ^^^^^^^^^^^^

or this:

... VALUES(1001,201,'2010-06-12','No  problems')
                    ^^^^^^^^^^^^

answered Dec 15, 2017 at 19:43

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting oracle sql script to mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related