| update if exists else insert [message #189259] |
Wed, 23 August 2006 18:50  |
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 #189432 is a reply to message #189259] |
Thu, 24 August 2006 10:10  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
