SQL Server query to Oracle query conversion

Question

I need to write a query in Oracle, but I'm more familiar with SQL Server.

In SQL Server, the query would look as follows: (simplified)

if exists (
    select * from table where a=1
)
begin
    update table set b=1 where a=1 
end else 
begin 
    insert table (a,b) values(1,1) 
end

Thanks for any help :)

===============================================================================

This is the Merge option, (I think):

MERGE INTO table T
USING (
    SELECT a,b
    FROM table
) Q
ON T.a = Q.a
WHEN MATCHED THEN
    UPDATE SET T.a = 1
WHEN NOT MATCHED THEN
    INSERT table (a,b) VALUES (1,1);

Is this correct?

What does that mean? Can you give an example relating to the above SQL? Thanks — CompanyDroneFromSector7G
– CompanyDroneFromSector7G, Commented Mar 7, 2013 at 11:01
Stackoverflow can give plenty of them to you. ;) stackoverflow.com/search?q=oracle+merge — Plouf
– Plouf, Commented Mar 7, 2013 at 11:02
I added what I think is the right usage of MERGE - does this look right? Thanks — CompanyDroneFromSector7G
– CompanyDroneFromSector7G, Commented Mar 7, 2013 at 11:14

JodyT · Accepted Answer · 2013-03-07 11:37:32Z

2

This should be the correct syntax for Oracle 11g. I'm not an expert on Oracle so maybe someone else could explain it better, but I believe the dual table is used when inserting new values instead or trying too merge is from another table.

MERGE INTO table1 T
USING (
    SELECT 1 a, 1 b FROM dual
) Q
ON (T.a = Q.a)
WHEN MATCHED THEN
    UPDATE SET b = 1
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES (Q.a,Q.b);

Working example

answered Mar 7, 2013 at 11:37

JodyT

4,4222 gold badges22 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

SQL Server query to Oracle query conversion

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