Home » SQL & PL/SQL » SQL & PL/SQL » update if exists else insert
update if exists else insert [message #189259] Wed, 23 August 2006 18:50 Go to next message
Jolanda
Messages: 68
Registered: September 2004
Member
Hello you all,

Hello you all,


I have two tables. Table a and table b. I have to update table b with records from table a. However, if the record does not exist I have to insert it.
This is done in dynamic sql.

How can I solve this problem?

Hope someone can help me out.
my dynamic sql looks like this:


'UPDATE ' || p_tablename_a || ' SET '
          || code || ' = ' || 15
          || name || ' = ' || 'me'
	  || ' WHERE '|| p_tablename_b eg.
		
EXECUTE IMMEDIATE v_update;




Thanks in advance.
Re: update if exists else insert [message #189262 is a reply to message #189259] Wed, 23 August 2006 20:08 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Take a look at the MERGE command.
Re: update if exists else insert [message #189432 is a reply to message #189259] Thu, 24 August 2006 10:10 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
If the merge command doesn't work for you, then something like this might do the trick. Use sql%rowcount to see if you updated any rows and then take the appropriate action from there.

declare
l_sql varchar2(2000);
p_tablename_a varchar2(30) := 'tablea';
p_tablename_b varchar2(30) := 'tableb';
l_old_name varchar2(30) := 'joe';
l_new_name varchar2(30) := 'me';
l_new_code number := 15;
begin

l_sql := 'UPDATE ' || p_tablename_a || ' SET '
|| 'code = :newCode, '
|| 'name = :newName '
|| ' WHERE name = :oldName';

dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql using l_new_code, l_new_name, l_old_name;

if sql%rowcount = 0
then
dbms_output.put_line('do insert instead');
end if;


end;
Re: update if exists else insert [message #189885 is a reply to message #189259] Mon, 28 August 2006 05:51 Go to previous message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

I Hope this will be the solution you are looking for.

SQL> CREATE TABLE TABLEA
2 (
3 SLNO VARCHAR2(10),
4 NAME VARCHAR(100)
5 );

TABLE created.

SQL> CREATE TABLE TABLEB
(
SLNO VARCHAR2(10),
NAME VARCHAR(100)
)

TABLE created.

SQL> INSERT INTO TABLEA ( SLNO, NAME ) VALUES ('1231', 'Venkat');

1 ROW created.

SQL> INSERT INTO TABLEA ( SLNO, NAME ) VALUES ('1232', 'Raju');

1 ROW created.


SQL> INSERT INTO TABLEB ( SLNO, NAME ) VALUES ('1231', 'Venkat');

1 ROW created.

SQL> INSERT INTO TABLEB ( SLNO, NAME ) VALUES ('1232', 'Raju');

1 ROW created.

SQL> INSERT INTO TABLEB ( SLNO, NAME ) VALUES ('1233', 'Srinu');

1 ROW created.

SQL> INSERT INTO TABLEA ( SLNO, NAME ) VALUES ('1234', 'Vinay');

1 ROW created.

SQL> COMMIT;

COMMIT complete.

DECLARE
l_slno_v VARCHAR2(10) := '1231';
BEGIN
UPDATE TABLEA
SET name = (SELECT NAME FROM TABLEB WHERE SLNO = TABLEA.SLNO)
WHERE SLNO = l_slno_v;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO TABLEA
SELECT *
FROM TABLEB
WHERE SLNO = l_slno_v;
END IF;

COMMIT;
END;

PL/SQL procedure successfully completed.

Check this if you want to update for known value.

With Regards
Venkat
Previous Topic: UTL_FILE - Invalid_Operation
Next Topic: Need a query
Goto Forum:
  


Current Time: Sat Dec 10 12:32:22 CST 2016

Total time taken to generate the page: 0.21440 seconds