Hello,
I am using mysql database and one of my tables need to be refreshed for every
request for that i have to empty the entire table before inserting any value.
Any suggesting how to empty the entire table. if i do not specify the "WHERE" clause will that work?
Thanks for the suggestions.
Thanks for the solution. I have another question though i did not want to waste another thread.
Is there any quick better way to check if an entry exists in the mysql database table?
I want to update my table so I want to enter only the entries which are not present . I guess findByPrimaryKey() will do but i have no pimary key in my table.
thankyou
If you want to find out if the table is empty or not, then try a query that only reads 1 record. If it returns 1 record then the table is not empty.
If you want to know if a specific entry is in the table or not, then try a query that reads that specific entry. If there is nothing you can put in your WHERE clause that reads a specific entry, then you can't know that without fixing your database design.
> As a general rule of thumb the DELETE
> statement is slower and safer than the TRUNCATE
> statement.
Slower, yes. Truncate generally avoids carrying out a table scan. But safer? As far as I recall truncate honours relational constraints, so I don't quite see that...?
I have an array of elements which i have to check if they exist and delete all the other entries in the table which do not match any one of the elements in my array.
If i put in a for loop and check each element only the last element in the array will be inserted at the end of the loop.
I am not getting any logic how to do it and was wondering if there is any technique for this
Thanks again.
> So what's the point of having rows (ones where this
> value is null) that can't be uniquely identified?
For that I have to explain what my application is about. Each user in my application has his/her own briefcase to store documents.
Each document has a unique identifier number. I only store the document number in the table which is called "Briefcase" the actual document is stored in another server.
Now when user enters into my application. I pass a request to another server and get all the object numbers of the documents in his/her briefcase(which can be empty if no documents are stored by the user). The briefcase is constantly updated if user stores new or deletes an element.
The server which stores the documents (i have no control on it) is the one which pushes the briefcase entries(only the number) every time it has been updated. I receive these entries in an array and have to store them in the Briefcase table of mine.
So i need to check if any of the objects which i received from the server are already in the Briefcase table if not present then i insert the element. If there is I do not.
In other words I have to delete all the other entries which do not match my array elements before inserting new ones
I know it has grown to be complicated but i hope some one understands and has a solution for it. I have been struggling for 3 days to figure out
I'm sorry, but I still don't understand why you don't have a PK on this table.
Either you can identify the rows that you want to delete, or you can't. If you can, where's the problem deleting them?
You have three basic options. You can:
i. Delete all the entries and re-insert them
ii. Delete all the entries where the entry is NOT identified by the list that you have available.
iii. Flag unwanted entries with an aditional column.
You've decided to go for ii. That requires you to be able to identify rows in the table. Without a PK you can't do this. It doesn't have to have a PK constraint, but it does need to uniquely identify the row.
Given a suitable PK, you can use the NOT IN syntax to zap the ones you don't want:
DELETE FROM FOO WHERE ID NOT IN ( 1, 2, 3, ... )
There will likely be constraints on how many entries you can use in this latter syntax, in which case you can alternatively insert the appropriate entries into a temp table, then do something like this (depends on your DB's temp table syntax):
DELETE FROM FOO WHERE ID NOT IN ( SELECT ID FROM #BAR)
But without a PK you're screwed - but then a table without a PK is broken anyway.
> > As a general rule of thumb the DELETE
> > statement is slower and safer than the TRUNCATE
> > statement.
>
> Slower, yes. Truncate generally avoids carrying out a
> table scan. But safer? As far as I recall truncate
> honours relational constraints, so I don't quite see
> that...?
As I recall in either MS SQL Server or Oracle a truncate could not be rolled back. Some would consider that unsafe.
> > As a general rule of thumb the DELETE
> > statement is slower and safer than the TRUNCATE
> > statement.
>
> Slower, yes. Truncate generally avoids carrying out a
> table scan. But safer? As far as I recall truncate
> honours relational constraints, so I don't quite see
> that...?
- TRUNCATE honors constraints but you can not truncate a parent table with constraints on it.
- TRUNCATE does not trigger row level events like a DELETE trigger.
- TRUNCATE can not be part of a transaction
I personally always use the DELETE statement within applications, I only use TRUNCATEs in system administration tasks, like large scale conversions.
> - TRUNCATE honors constraints but you can not
> truncate a parent table with constraints on it.
> - TRUNCATE does not trigger row level events like a
> DELETE trigger.
> - TRUNCATE can not be part of a transaction
Thanks for that (Ditto to jschell). I guess this shows that I've never used Truncate for anything other than initial data setup tasks. You learn something new ever day.
