SAJJAS - Knowledgebase

Scalar sub query UPDATE x SET foo = (SELECT y.foo FROM y WHERE y.bar = x.bar); Scalar sub query with existential predicate UPDATE x SET foo = ( SELECT y.foo FROM y WHERE y.bar = x.bar) WHERE EXISTS ( SELECT y.foo FROM y WHERE y.bar = x.bar); Updatable view (requires foreign key from x to y) UPDATE ( SELECT x.foo AS old_foo, y.foo AS new_foo FROM x, y WHERE y.bar = x.bar) SET old_foo = new_foo; Merge Update MERGE INTO x USING (SELECT foo, bar FROM y ) y ON (y.bar = x.bar) WHEN matched THEN UPDATE SET x.foo = y.foo; Note: WHEN NOT MATCHED construct is optional

Modifying a column data type or size in Oracle is done by using the constructs below Changing the column data type: ALTER TABLE <Table name> MODIFY ( <Column Name> <New data type>) Changing the size of a column: (Assuming the previous data type of column was varchar2 with the length of 20 and it needs to be changed to 10) ALTER TABLE <Table name> MODIFY ( <Column Name> VARCHAR2(10)) Note: Text within <> indicate the parameters that need to be replaced.

Renaming a column in Oracle is done by using the construct below ALTER TABLE <Table name> RENAME COLUMN <old column name> TO <new column name> Note: Text within <> indicate the parameters that need to be replaced.