Home » SQL & PL/SQL » SQL & PL/SQL » INSERT OR UPDATE
INSERT OR UPDATE [message #355875] Tue, 28 October 2008 04:49 Go to next message
cotoga
Messages: 2
Registered: October 2008
Junior Member
Hi all,

i have a little problem.

in my database exists a table with SQL-Statements which are executed daily. Also "Grant"-Statements.

To get an overview, which user have rights on which table, i wrote a short query.
You see, I'm german (look at query Smile ).

The table columns:
BENUTZER (USER) PKEY/IDX
TABELLE (TABLE) PKEY/IDX
RECHTE (RIGHTS)
KOMMENTAR (COMMENT)

The comment field will be filled manually by me.

An my problem is now, to fill the table with data without violation due to double entrys (user/table).
When the entry exists, the query should update it, else insert a new one.
But I don't know a procedure for "insert or update"


"INSERT INTO OR UPDATE" ... USER_TABLE (BENUTZER,TABELLE,RECHTE)
FROM
(
SELECT
SQLCODE
,substr(SQLCODE,instr(SQLCODE,' TO ')+4,length(SQLCODE)-instr(SQLCODE,' TO ')+4) AS BENUTZER
,substr(SQLCODE,instr(SQLCODE,' ON ')+4,instr(SQLCODE,' TO ')-instr(SQLCODE,' ON ')-4) AS TABELLE
,substr(SQLCODE,7,instr(SQLCODE,'ON')-7) AS RECHTE
FROM AA_SUP_SQL_BATCH
WHERE SQLCODE LIKE 'GRANT%'
)


Perhaps anyone can help me.

Kind regards
Stephan
Re: INSERT OR UPDATE [message #355883 is a reply to message #355875] Tue, 28 October 2008 05:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606

Regards

Raj
Re: INSERT OR UPDATE [message #355893 is a reply to message #355875] Tue, 28 October 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before your next post, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: INSERT OR UPDATE [message #355896 is a reply to message #355875] Tue, 28 October 2008 06:23 Go to previous message
cotoga
Messages: 2
Registered: October 2008
Junior Member
Many thanks it works fine. Smile

I didn't know this function.

Next time i will post in the right format.

[Updated on: Tue, 28 October 2008 06:25]

Report message to a moderator

Previous Topic: what the (+) means?
Next Topic: What procedure was originally called
Goto Forum:
  


Current Time: Thu Dec 08 08:33:51 CST 2016

Total time taken to generate the page: 0.11248 seconds