UPDATE OR INSERT INTO with Existing Table Values

Dev Shed Forums Sponsor:

May 11th, 2009, 03:41 AM

biju_ps

Contributing User

Join Date: Aug 2008

Posts: 61

Time spent in forums: 1 Day 4 h 33 m 7 sec
Reputation Power: 5

UPDATE OR INSERT INTO with Existing Table Values

Hi,
Is it possible to use UPDATE OR INSERT INTO to update a table using the existing field value in FB 2.1 .

For eg. update the employee table salary with salary + 100 if the record is alreay exist , add a new record with salary 100 if record is not found

it should be something like

SQL Code:

Original - SQL Code
UPDATE OR INSERT INTO Employee (First_name,Last_Name, Salary) Values ('Fname','Lname', Salary + 100) MATCHING (First_name,Last_Name);

 
UPDATE OR INSERT INTO  Employee (First_name,Last_Name, Salary)
VALUES ('Fname','Lname', Salary + 100)
 MATCHING (First_name,Last_Name);

thx

May 11th, 2009, 08:40 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Shure it can, see here for an example.

Ooops, sorry, I read more carefully your example and the insert part would not work, alone or in the merge statement, so no, but this wouldn't work in any other database.

Update 2: The MERGE statement would work, should be something like

Code:

merge into employee e using (select * from employee) cd
on (e.first_name = cd.first_name and e.last_name = cd.last_name)
when matched then
update set first_name = cd.first_name,
last_name = cd.last_name,
salary = e.salary + 100
when not matched then
insert (last_name, first_name, salary)
values ('first_name', 'last_name', 0);

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

Last edited by pabloj : May 11th, 2009 at 08:52 AM.

May 11th, 2009, 08:52 AM

biju_ps

Contributing User

Join Date: Aug 2008

Posts: 61

Time spent in forums: 1 Day 4 h 33 m 7 sec
Reputation Power: 5

Quote:

Originally Posted by pabloj

OK.
Thanks for your immediate reply..

May 11th, 2009, 08:56 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Sorry, this can be done, a better example is:

Code:

merge into employee e using (
select first_name, last_name, salary from employee
union all
select 'pippo', 'pluto', 5 from RDB$DATABASE) cd
on (e.first_name = cd.first_name and e.last_name = cd.last_name)
when matched then
update set first_name = cd.first_name,
last_name = cd.last_name,
salary = e.salary + 100
when not matched then
insert (e.last_name, e.first_name, e.salary)
values ('first_name', 'last_name', 0);

Thanks for the interesting problem

Last edited by pabloj : May 11th, 2009 at 09:00 AM.

May 11th, 2009, 09:09 AM

biju_ps

Contributing User

Join Date: Aug 2008

Posts: 61

Time spent in forums: 1 Day 4 h 33 m 7 sec
Reputation Power: 5

Quote:

Originally Posted by pabloj

Sorry, this can be done, a better example is:

Code:

merge into employee e using (
select first_name, last_name, salary from employee
union all
select 'pippo', 'pluto', 5 from RDB$DATABASE) cd
on (e.first_name = cd.first_name and e.last_name = cd.last_name)
when matched then
update set first_name = cd.first_name,
last_name = cd.last_name,
salary = e.salary + 100
when not matched then
insert (e.last_name, e.first_name, e.salary)
values ('first_name', 'last_name', 0);

Thanks for the interesting problem

Sorry .. its not working due to some constraint validation.. can you please give me a working example

thx again

May 11th, 2009, 09:17 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Hem, as long as you understand how the update clause of the merge uses both the existing (alias e) and new (alias cd) set of values

Code:

...
update set first_name = cd.first_name,
last_name = cd.last_name,
salary = e.salary + 100
...

the problem is solved.
Of course this is done on my employee table, which might differ from yours and from the one of firebird sample database.

I think adapting it is not too much job on your side.

May 11th, 2009, 09:24 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Ok, things are slow at the office today ...
here is my table structure and sample data:

Code:

CREATE TABLE EMPLOYEE
(
  FIRST_NAME Varchar(50) NOT NULL,
  LAST_NAME Varchar(50) NOT NULL,
  SALARY Integer DEFAULT 0 NOT NULL,
  E_NUM Integer
);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON EMPLOYEE TO  SYSDBA WITH GRANT OPTION;

INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, SALARY, E_NUM)
 VALUES (
'aaa', 
'bbb', 
10, 
1
);

May 11th, 2009, 09:42 AM

biju_ps

Contributing User

Join Date: Aug 2008

Posts: 61

Time spent in forums: 1 Day 4 h 33 m 7 sec
Reputation Power: 5

I have solved it as by adopting your code

merge into country c using (
select 'Ind' as Country, 'Rs.' as Currency, 5 as Os from RDB$DATABASE) cd
on (c.country = cd.country and c.currency = cd.Currency)
when matched then
update set c.Country = cd.Country,
currency = cd.currency,
os = c.Os + 1000
when not matched then
insert (c.Country, c.currency, c.OS)
values ('Ind', 'Rs.', 100);

to run this , i have added a field Os (numeric)

Thanks a lot & regards
Biju PS

May 28th, 2009, 02:42 AM

alikazim

Registered User

Join Date: May 2009

Posts: 11

Time spent in forums: 1 h 46 m 5 sec
Reputation Power: 0

Can it be done in Firebird 1.5

Hi All,

I need to merge the two tables based on a criteria without creating duplicate. I tried the code mentioned above but it did not work.

I'm using Firebird 1.5. Is there a way to do it in 1.5? I read somewhere about ON DUPLICATE KEY UPDATE, but I guess its for MySQL.

Any suggestions will be highly appreciated.

Regards,
AKG

#10

May 28th, 2009, 03:40 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Not in 1.5, which is quite old BTW.
The MERGE synthax is much better (powerful and flexible) than ON DUPLICATE KEY UPDATE imho

#11

May 28th, 2009, 04:27 AM

alikazim

Registered User

Join Date: May 2009

Posts: 11

Time spent in forums: 1 h 46 m 5 sec
Reputation Power: 0

Hi Pabloj,

Thanks for your reply. So does that mean there is noway we can achieve the same functionality in FB1.5? I mean to ask... can we achieve the same with the help of some stored procedure (making use of cursor etc.) or trigger to give us the same result?

I cant upgrade the database due to application compatability issue.

#12

May 28th, 2009, 04:32 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Quote:

Originally Posted by alikazim

... can we achieve the same with the help of some stored procedure (making use of cursor etc.) or trigger to give us the same result?

Shure, two queries can do the trick (if you are merging two tables) or a stored proc and a cursor.
I suspect the two queries will be faster than the stored procedure.

#13

May 28th, 2009, 05:23 AM

alikazim

Registered User

Join Date: May 2009

Posts: 11

Time spent in forums: 1 h 46 m 5 sec
Reputation Power: 0

Can we have an example/sample SQL code? You can use the Employee table mentioned above.

I have another table Employee2 which may be having newer Employees or existing employees with updated salaries. I want to update my original Employee table in such a manner that it contain not only the original employees with new/updated salary as well as new employees from Employee 2 table.

It's like one way sync.

Thanks once again for your help.

#14

May 28th, 2009, 06:48 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,393

Time spent in forums: 3 Months 4 Days 23 h 21 m 37 sec
Reputation Power: 532

Quote:

Originally Posted by alikazim

You can use the example above, the one on my blog or write your own and post it here with questions which might arise.

#15

May 31st, 2009, 02:21 AM

alikazim

Registered User

Join Date: May 2009

Posts: 11

Time spent in forums: 1 h 46 m 5 sec
Reputation Power: 0

Hi,

I have made a Stored Procedure which use cursor to insert/update records in table EMPLOYEE1, but its giving me syntax error. Can you help? I'm new cursors...

CREATE TABLE EMPLOYEE1
(
ID Integer NOT NULL,
NAME Varchar(50) NOT NULL,
SALARY Integer DEFAULT 0 NOT NULL
);

CREATE TABLE EMPLOYEE2
(
ID Integer NOT NULL,
NAME Varchar(50) NOT NULL,
SALARY Integer DEFAULT 0 NOT NULL
);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON EMPLOYEE1 TO SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON EMPLOYEE2 TO SYSDBA WITH GRANT OPTION;

INSERT INTO EMPLOYEE1 (ID, NAME, SALARY)
VALUES (1, 'AAA', 1200);

INSERT INTO EMPLOYEE2 (ID, NAME, SALARY)
VALUES (2, 'BBB', 1000);

INSERT INTO EMPLOYEE2 (ID, NAME, SALARY)
VALUES (1, 'AAA', 1500);

CREATE PROCEDURE MERGE_TABLE
AS

DECLARE VARIABLE ID INTEGER;

DECLARE VARIABLE NAME VARCHAR(50);

DECLARE VARIABLE SALARY INTEGER;

BEGIN

FOR SELECT ID,

Name,

Salary

FROM Employee2

WHERE ID IN (SELECT ID FROM Employee1)

INTO :ID,

:Name,

:Salary

AS CURSOR EMPCURSOR -- To Update Salary of existing employees

DO BEGIN

UPDATE Employee

SET Salary = :Salary

WHERE CURRENT OF EMPCURSOR;

END

FOR SELECT ID,

Name,

Salary

FROM Employee2

WHERE ID NOT IN (SELECT ID FROM Employee1)

INTO :ID,

:Name,

:Salary

AS CURSOR EMPCURSOR -- To Insert record of new employees

DO BEGIN

UPDATE Employee

SET Salary = :Salary

WHERE CURRENT OF EMPCURSOR;

END

END

Data before running the stored proc:

EMPLOYEE1
---------
1 AAA 1200

EMPLOYEE2
---------
1 AAA 1500
2 BBB 1000

Data after running the stored proc: (I wasn't able to get it, but this is what I want to achieve). It's like one way sync.

EMPLOYEE1
---------
1 AAA 1500
2 BBB 1000

EMPLOYEE2
---------
1 AAA 1500
2 BBB 1000

Regards,
AKG

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: