Link to home
Start Free TrialLog in
Avatar of azsd
azsd

asked on

how to use an IF statement in oracle's INSERT syntax like mssql server?

hi,
I am using mssql with oledb,
I have this sql text like:

;===========================================
IF (select count(*) from table_user where userid=1700))=0
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
;===========================================

now our database moved to oracle 9i,
I use the "if" like this,sure its only occur an oledberror,nothing inserted.

;============================================
IF NOT EXISTS(select userid from table_user where userid=1700) THEN
INSERT INTO table_user (userid,username)
VALUES (1700,'azsd')
END IF
;============================================

I have another idea plan to use when state ments to insert the exists user into an other temp table but i think my methods were so ugly.

how can I do an check before insert in one query?

thx.
Avatar of RaisinJ
RaisinJ

Decalre another variable...

@User_ID    Table_User.UserID%Type;

SELECT NVL(UserID,0)
INTO @User_ID
FROM Table_User
WHERE User_ID = 1700

IF @User_ID = 0
    INSERT INTO Table_User (UserID, UserName)
    VALUES (1700,'@YourUserName');
END IF;
Avatar of azsd

ASKER

thx your reply

I only can using query command,store procedure/function not suitable for my programing library....
it seems kololedb don't have that feather....

seems your solution are only suite in procedure,in sql plus it said:

@User_ID    table_user.UserID%Type;
*
Line 1 occured error:
ORA-00900: invalied SQL statements....

The @ symbols is used in MS SQL Server to identify variables.
In Oracle the @ symbol is used as a short hand for the 'start' command which executes script files.

Try the above solution without the @ symbols.

Sorry, in your variable declaration, you cannot use the "@" symbol.  If you did, try doing it without the "@" symbol.
SOLUTION
Avatar of grim_toaster
grim_toaster

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of azsd

ASKER

yes,by little modify,these two nice script works!
Avatar of azsd

ASKER

thx all yours,
I am going to modify my codes now.