[Solved] Add data from Calc overwriting the duplicates

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] Add data from Calc overwriting the duplicates

Postby Don.a.dio » Sun Jul 15, 2012 11:30 am

When I paste data from Calc to a Base table, I would like to overwrite the IDs already found in the Base table with the new data from Calc. All I seem to be able to do is skip these records. Is it possible to merge data as well? :?
Last edited by Hagar Delest on Mon Jul 16, 2012 10:29 pm, edited 1 time in total.
Reason: tagged [Solved].
Mac OSx 10.6.8
Open Office 3.4.0
Don.a.dio
 
Posts: 3
Joined: Sun Jul 15, 2012 11:15 am

Re: Add data to Base table rom Calc overwriting the duplicat

Postby Villeroy » Sun Jul 15, 2012 11:56 am

Simply dump the sheet data to another database table and run an SQL UPDATE command.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add data to Base table rom Calc overwriting the duplicat

Postby Don.a.dio » Sun Jul 15, 2012 1:14 pm

Thank you very much, I feel like I've made a great breakthrough in my beginner's understanding of databases and SQL.

I just need some help getting my command right.
My 2 tables are:
Tabella1
Tabella2

I want all the common IDs between the 2 tables to be updated in Tabella1 with the data for those IDs in Tabella 2. I found this command which worked, but it would require specifying each and every field and ID.

UPDATE "Tabella1"
SET "LastName" = ( SELECT "LastName" FROM "Tabella2" WHERE "ID" = '1' )
WHERE "ID" = 1

Is it possible to write the command to update thousands of records without specifying each and every one of their IDs?
Mac OSx 10.6.8
Open Office 3.4.0
Don.a.dio
 
Posts: 3
Joined: Sun Jul 15, 2012 11:15 am

Re: Add data to Base table rom Calc overwriting the duplicat

Postby Villeroy » Sun Jul 15, 2012 1:53 pm

It depends on the type of database. No, Base is not a database. If the status bar of your Base document reads "embedded HSQLDB" then you are working with OpenOffice on top of this database software: http://hsqldb.org/doc/1.8/guide/ch09.html

Code: Select all   Expand viewCollapse view
UPDATE "Tabella1" AS "T1"
SET "T1"."LastName" = ( SELECT "T2"."LastName" FROM "Tabella2" AS "T2" WHERE "T1"."ID" = "T2"."ID" )


should work if the imported sheet data have integer ID numbers. Unfortunately, spreadsheet data tend to be inconsistent and somewhat "dirty".
... WHERE "T1"."ID" = CAST("T2"."ID" AS "INT") ) may work if text IDs from the sheet are convertible.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add data to Base table rom Calc overwriting the duplicat

Postby Don.a.dio » Mon Jul 16, 2012 10:29 am

Thank you very much. The command you provided works perfect. I will look over the documentation for HSQLDB; it's a great resource. This forum has also been extremely helpful. Do you have a Paypal account for donations?
Mac OSx 10.6.8
Open Office 3.4.0
Don.a.dio
 
Posts: 3
Joined: Sun Jul 15, 2012 11:15 am


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest