CODEWITHSUNDEEP
sqloracle-database
CompanyDroneFromSector7G
CompanyDroneFromSector7G

Reputation: 4517

SQL Server query to Oracle query conversion

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?

Upvotes: 0

Views: 210

Answers (1)

JodyT
JodyT

Reputation: 4412

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

Upvotes: 2

Related Questions