we use different databases for test (mysql) and production (oracle) systems. Is there a common sql statement for updating a row if it exists and insert if not?
Regards, Marc
The answer is NO. For MYSQL it will be different and for Oracle it will be different.
In MYSQL it would be like
INSERT INTO tabelname (id, name)
VALUES (1, 'abc')
ON DUPLICATE KEY UPDATE id = id;
In Oracle it would be like
DECLARE
x NUMBER:=0;
BEGIN
SELECT nvl((SELECT 1 FROM tabelname WHERE name = 'abc'), 0) INTO x FROM dual;
IF (x = 1) THEN
INSERT INTO tabelname (1,'abc')
END IF;
END;
or you can use merge like this:
merge into tablename a
using (select 1 id, 'abc' name from dual) b
on (a.name = b.name)
when not matched then
insert( id, name)
values( b.id, b.name)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments