Database

Compare two tabel and add/update query

23-Jun-14 1:04

I'm with the other 2, their arguments are perfectly valid, and here is another, triggers spit are a support nightmare, digging out an error in a nested trigger can drive you nuts.

Triggers spit have their place, they are excellent for auditing and logging changes to your data ONLY.

Never underestimate the power of human stupidity
RAH

member002618-Jun-14 20:44

member0026

18-Jun-14 20:44

Hi Team,

I need to compare two tables and do the add/update functionality.If the Table2 have any new records it should be added in the Table1 and if the Table1 have same records which is in Table2, its should be updated.

Table1
ID Name Mark
1 Kishor 56
2 Amit 78
4 Vinay 58

Table2
ID Name Mark
2 Amit 67
6 Raj 89
1 Kishor 98

Output
ID Name Mark
1 Kishor 98
2 Amit 67
4 Vinay 58
6 Raj 89

Re: Compare two tabel and add/update query

Mycroft Holmes18-Jun-14 22:33

Validate Date Time value on Sqlite before insert / update

18-Jun-14 22:33

It would be useful to know which database you are using. Assuming SQL Server then here is some google foo[^]

Never underestimate the power of human stupidity
RAH

rahmat198517-Jun-14 22:29

rahmat1985

17-Jun-14 22:29

as we all know that the correct format for date and time data in sqlite is :

2014-06-18 01:00:00

my question : is there any method to validate date and time data before insert / update on sqlite, maybe when making table, to make sure date and time data is correct, thanks

Re: Validate Date Time value on Sqlite before insert / update

Mycroft Holmes17-Jun-14 22:57

Re: Validate Date Time value on Sqlite before insert / update

17-Jun-14 22:57

You should be storing the data as a datetime TYPE not a string, therefore convert (TryParse) your string input to a datetime and pass it to the database in the correct data type using a paramaterised query.

If it fails the tryparse it is invalid

Never underestimate the power of human stupidity
RAH

Eddy Vluggen18-Jun-14 0:28

Re: Validate Date Time value on Sqlite before insert / update

18-Jun-14 0:28

Mycroft Holmes wrote:
You should be storing the data as a datetime TYPE not a string

SQLite does not have a DATETIME datatype. I'd recommend storing it as an integer, as recommend on their website[^].

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes18-Jun-14 12:44

Re: Validate Date Time value on Sqlite before insert / update

18-Jun-14 12:44

Eddy Vluggen wrote:
SQLite does not have a DATETIME datatype

Now that I did not know.

Never underestimate the power of human stupidity
RAH

Bernhard Hiller19-Jun-14 22:26

Bernhard Hiller

19-Jun-14 22:26

Eddy Vluggen wrote:
SQLite does not have a DATETIME datatype

Hard to believe, but true. Poor guys who have to deal with such crap!

Re: Validate Date Time value on Sqlite before insert / update

Eddy Vluggen20-Jun-14 0:30

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 0:30

Bernhard Hiller wrote:
Hard to believe, but true. Poor guys who have to deal with such crap!

I prefer this crap over SqlCe, MS Access, XML and Excel as a single-file database.

A DateTime is usually merely a double; a boolean is usually merely a bit. SQLite is lightweight; you won't find a XML, GUID nor a Spatial datatype either - because all those types can be derived from what is already there.

The only thing that seemed weird is that the bool is associated with a numeric type, and not an integer-type.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

PIEBALDconsult20-Jun-14 9:45

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 9:45

I'll stick with SqlCe when given a choice; better to have features I don't use than miss features I want.

And have you looked at Sql Server LocalDB?

You'll never get very far if all you do is follow instructions.

Eddy Vluggen20-Jun-14 10:01

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 10:01

PIEBALDconsult wrote:
And have you looked at Sql Server LocalDB?

I tried, but failed at downloading[^] it.

PIEBALDconsult wrote:
I'll stick with SqlCe when given a choice; better to have features I don't use
than miss features I want.

So you'll be replacing all those SqlCe's with Sql-express versions?

SqlCe and SQLite are a close match; but I like to run my code as well on my OpenSUSE desktop and there's no SqlCe there Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

PIEBALDconsult20-Jun-14 10:30

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 10:30

Eddy Vluggen wrote:
replacing all those SqlCe's with Sql-express versions

No. The other way around. Big Grin | :-D

I use Express for a few things, but for one of my projects I wanted to be able to have the app on a flash drive and not require a server/service/daemon running. That way it can be carried around and used whereever. So now I allow the app to use Ce or regular Sql Server, whichever the user wants to use. All the tables and SQL statements are the same (including DATEs and GUIDs), only the Provider is different.

You'll never get very far if all you do is follow instructions.

Eddy Vluggen20-Jun-14 10:45

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 10:45

..and might even work relative happily using a different provider[^] on a Raspberry Pi Big Grin | :-D

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

PIEBALDconsult20-Jun-14 9:43

Re: Validate Date Time value on Sqlite before insert / update

20-Jun-14 9:43

Bernhard Hiller wrote:
have to deal with such crap

Seems a choice to me.

You'll never get very far if all you do is follow instructions.

Eddy Vluggen18-Jun-14 0:29

Re: Validate Date Time value on Sqlite before insert / update

18-Jun-14 0:29

No, there isn't. You validate it in code before inserting/updating.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

jschell18-Jun-14 11:23

jschell

18-Jun-14 11:23

Steps:
Choose the data type that it is actually going to be stored as.
Take a value and use on of the sqllite functions to convert it to a different type.
Take that value and convert it back to the original type.
If they are the same then it is valid.

So if you start with text, convert it to a number, then convert the number back to text and compare the original text and the final text.

Re: Validate Date Time value on Sqlite before insert / update

PIEBALDconsult20-Jun-14 9:46

20-Jun-14 9:46

rahmat1985 wrote:
as we all know

We don't all know that.

You'll never get very far if all you do is follow instructions.

SQL search question

Jassim Rahma16-Jun-14 22:14

Jassim Rahma

16-Jun-14 22:14

I have the following table in MySQL:

table name : contacts

Field:
contact_name
company_name
service_offered

I want the user to be able to search the contact table in this way:

if the user entered

John Dell

then it should search all fields for any of the given words

so if John is the first name and Dell is company
or John Dell is the name (contains)
or John is the name and Dell is the service

etc...

can anyone tell me how can I do such SQL?

Thanks,
Jassim

Technology News @ www.JassimRahma.com

Eddy Vluggen16-Jun-14 22:29

16-Jun-14 22:29

Free text search[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

NitinDhapte16-Jun-14 23:08

NitinDhapte

16-Jun-14 23:08

Dear Jassim,

If you know the exact column list to search data

For Example :
1. company_name
2. first_name
3. last_name

Then you can write down your query as below :

SQL

SELECT * FROM contacts WHERE (company_name like '%'+@searchText+'%' OR first_name like '%'+@searchText+'%' OR last_name like '%'+@searchText+'%')

Secondly, If you don't know about the column list then let me know the I can write a dynamic query for you.

Shameel17-Jun-14 0:48

Shameel

17-Jun-14 0:48

It would be something like this:

SQL

SELECT * 
FROM contacts
WHERE (contact_name LIKE '%John%' OR contact_name LIKE '%Dell%')
 OR (company_name LIKE '%John%' OR company_name LIKE '%Dell%')
 OR (service_offered LIKE '%John%' OR service_offered LIKE '%Dell%')

Basically, you'd split the search text into separate words and dynamically build the query above to include all the words.