SQL Questions

[ahmad]

I am lazy and I do not want to update records. Since I have all my data outside the database, I can delete all the records and guarantee that no others are deleted and just make new ones again.

I don't know how bad this is on the DB though or how inefficient it is. Any thoughts?

I have got a bit of challange for you if you can do some SQL:

-Perform a query and get the record set
-If there is at least one record, return a column from that record

If you can do it in a single SQL statement, you will be my hero :)

[kiwi]

Which DBMS do you use? Why not just import all new data with update option?

[ahmad]

Yeah I could do an update, but I want to know if there is a problem with doing a delete/new record because I don't want to write up all the code for the update. The deletes will be about 5 lines each I already have the INSERT INTO statements setup so no work required there.

Its dumb how update has such different syntax from insert into.

Update x SET a = "" b = "" c = "" Where y = z

Why would they go and do that. And this application I am writing is already about 4000 lines of code so I am trying to cut corners.

[StyM]

i hope this helps...:D

update table set column='1' where 0 < (select count(column) from table where column = 'exists')

[Growly]

SQL queries will execute alot faster than any scripts you have coded because they rely on the server backend. AFAIK, using an update will already be cutting out overhead, so by avoiding it you are only making things slower.

How much of a problem this is will depend on how many records you're inserting.

Any SQL pros want to correct me? I've had this question before, but from the advice I was given (which makes sense) the more you leave to the database, the faster things are. After all, that's compiled code, and you're dealing with just-in-time line-by-line, higher level bloat scripts?

Interesting question: Which language are you using? It's not something you can compile like, say, .NET?

[ahmad]

SQL queries will execute alot faster than any scripts you have coded because they rely on the server backend. AFAIK, using an update will already be cutting out overhead, so by avoiding it you are only making things slower.

How much of a problem this is will depend on how many records you're inserting.

Any SQL pros want to correct me? I've had this question before, but from the advice I was given (which makes sense) the more you leave to the database, the faster things are. After all, that's compiled code, and you're dealing with just-in-time line-by-line, higher level bloat scripts?

Interesting question: Which language are you using? It's not something you can compile like, say, .NET?

.NET C#.

Here is a code paste to give you guys an idea:

http://rafb.net/p/NE2JXQ35.html

Efficiency, I am not too worried about. However I am worried about the structure of the database and what would happen from frequent deletes. In otherwords, is it healthy to do this?


i hope this helps...

update table set column='1' where 0 < (select count(column) from table where column = 'exists')

Thats pretty clever, but I am still trying to figure out exactly what it does. But why do you set column to 1?

[StyM]

ooops sorry i misundestood your reqs..

Thats pretty clever, but I am still trying to figure out exactly what it does. But why do you set column to 1?
it updates the column value to 1 or whatever you want, only if there is an existing record.




-Perform a query and get the record set
-If there is at least one record, return a column from that record


but based on the req above.. the sql should be..

select column1,column2 from table where column1 = 'exists'

[ahmad]

how about this:

select column from table where (select count(column) from table where x=y) > 1

Would that work?

[StyM]

would work only if there are 2 or more records. :D
just use this for simplicity... this would return if there are any records to satisfy x=y condition.. :D:D

select column from table where x=y;

[ahmad]

Yeah but then I would have to check the number of records then get my key :p:

[ahmad]

Actually after thinking about it, I think the delete might actually be healthy for the database. Main reason being when things are indexed in a database, modifying the data screws up the way its organized. So when I delete I make some room, and re-add the information which is inserted in the appropriate place.

But of course now we have the issue of wasted space, but I am not worried. It will take 20 years of daily input to get this db up to 50mb of usage.

[Deadeye550]

what type of database is this, doing an update in C# is no different then doing a delete or insert.
mysql?
SQLserver?
oracle?

doing a update is more efficient then doing a delete and insert, you're requiring the DBA to do twice as much work doing the latter.

In regards to the doing a delete being "healthy" to the database, that is false, a good database engine will re-index the data as soon as it gets changed.

Wesley

[ahmad]

A good database engine.. let us hope they all do this :)