Monday, July 28, 2008

Playing Oracle and MySQL game

Sometimes we want to escape characters in Oracle SQL Plus console

Set the escape character
set escape \

Send command (in the same session)
INSERT INTO customer (url) VALUES('http://www.example.com/redir.php?id=1234\&log=true');

Select rows recursevely

SELECT LPAD(' ', 2 * (LEVEL - 1)) || TO_CHAR(child) s
FROM test_table
START WITH parent IS NULL
CONNECT BY PRIOR child = parent;

This has just one restriction: no joins allowed.
source: http://www.adp-gmbh.ch/ora/sql/connect_by.html

Insert-update with MySQL

INSERT INTO table_name (col_names)
VALUES (values)
ON DUPLICATE KEY UPDATE col=val, col2=val2;
source: http://devblog.billism.com/?p=12

No comments: