Update Multiple columns in sql and oracle servers

Question

Is it possible to get common query for updating multiple columns in table in sql and oracle . Instead of modifying separate queries with provider type.

Update multiple columns in SQL

You've written SQL Server in your title and tagged Oracle? What is your question? — Ben, Jun 28 '13 at 09:43
I need a common query to update multiple columns of a table in sql server and oracle . — Rakesh Devarasetti, Jun 28 '13 at 09:45
I'm not well aware on how things work for Oracle, but MERGE should be the way for you to go. — Serge, Jun 28 '13 at 09:53

Surendra · Answer 1 · 2013-06-28T10:19:38.343

0

The standard ANSI SQL implementation will work with both sql server and oracle

UPDATE <TABLE Name>
SET Col1 = ColValue[, Col2 = Col2Value, .....];

The only thing that you have to put is a semicolon at the end of update statement, as ";" is mandatory statement terminating operator in oracle, where as it is optional in sql server.

NOTE: The clauses that I put in square brackets ([]) are optional.

NOTE 2: UPDATE..FROM is a properietry syntax used by SQL Server, so it cannot be accepted by oracle..

NOTE 3: UPDATE SET (col1,col2) = Col1,Col2 is a syntax supported by oracle but not by sql server.

edited Jun 28 '13 at 10:19

answered Jun 28 '13 at 09:58

Surendra

711
5
15

1

I think there's no FROM clause in the ANSI SQL. – Serge Jun 28 '13 at 10:05
@Serge You are correct there is no from clause in the ANSI syntax, my bad.. edited my solution above... – Surendra Jun 28 '13 at 10:16

score 0 · Accepted Answer · answered Jun 28 '13 at 11:42

0

I guess Oracle is compatible with SQL:2003

You could use

MERGE INTO tablename USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]

More about MERGE here

answered Jun 28 '13 at 11:42

Serge

6,554
5
30
56

Update Multiple columns in sql and oracle servers

2 Answers2