SQL questions 3

[pierrotsc]

So i am learning SQL with abosule database.
I would like to run the following syntax on all the records of a table. Right now, it works fine if I have one record only. What should i change to be able to run this command if the table has multiple recotds? I am only changing the value of one column. The value will be the same for the column in each records.
Thanks.

ABSQuery.SQL.Text := 'UPDATE "'+db.DatabaseFileName+'".xtra SET ProjectNO=:NewProjectNo';
ABSQuery.Params.ParamByName('NewProjectNo').AsInteger := TempProjectNo;
ABSQuery.ExecSQL;

 

[smays]

...first thing first. Even though I see the PASCAL snuck into your syntax, this is really an SQL related question, so you probably should have chosen a different forum.

Second, because there is no filter (WHERE clause), the UPDATE command in the SQL syntax you have above should change EVERY record without discrimination - 1 record or 1000 records. From your problem statement/question, it sounds as though that is what you want to have happen, so your query should do what I think you want it to do.

Third, I know nothing about Absolute Database, but if you can get your hands on a query browser or something similar, experimenting with SQL will be a lot more fruitful.

Unless I am mistaken, and if I am, please forgive me, at the moment you are trying to learn about databases/SQL queries AND how to create client programs, which is great. If this is true, and if you can't find a query browser of sorts, perhaps you can take some time from development and write your own simple query browser. About all you need is a TMemo to type in an experimental query and a TDBGrid to show the query results.

Steve.

 

[pierrotsc]

Steve, all your assumptions are correct. I did indeed assume that my query would only affect one record and have not tried to see if it would affect all records in a table. I will check it right now.
I think absolute Database may have a query browser or I will google for one.
Thanks again for your prompt answr.
Really appreciate it.
Pierrot

 

[pierrotsc]

Well, it does work but found out that I need to update the primary index with incremental values. So this time, i need to have a different value for each index of each records. You said to use a filter (Where clause).
For example if i have 3 records, i need to update the TRIALNO column with the lastautoinc value that was used for the fitst record, then increment every following record by adding one.

What command should I add to my query to be able to have a different value in each primary index column?

Thanks.
Pierrotsc

 

[lespaul]

so you're trying to create something like an autoincrementing field in your table as the primary key?

Leslie

 

[Glenn9999]

I don't understand exactly what you are trying to accomplish.

As for the update statement, smays is correct. Since SQL is a set-based language, not specifying a limiting clause (eg. WHERE) will make the update statement change every row in the targeted table.

The update clause generally has the statement segment to set and then the limiter. The fields that you update, unless you update a primary key (*), really don't matter all that much. What does matter is the where clause. You almost always want it to target a specific record. To do that, you want it to limit to the primary key alone. The primary key (as you might have guessed) is a key that uniquely identifies the record.

As for what the statement looks like:

UPDATE <MYTABLE>
   SET <MYFIELDNAME> = <MYVALUE>
 WHERE <PKFIELD> = <PKKEYVALUE>

I know most of what I typed was just random facts on the UPDATE topic, but hopefully it helped you along somehow, at least until the question can be clarified.

As a suggestion, you may want to get a situation where you're not working with any live data that you can't restore if you are still learning how to work with SQL. Other than that, the generic query processor will be useful for you to begin learning the language.

(*) = working with primary keys typically open up a huge kettle of fish regarding several issues. These issues can be solved, but to relate a full understanding of this topic would be much beyond this forum and should be reserved for a study text or classroom environment.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[pierrotsc]

Maybe, I should explain what i am trying to do first. I may be complicating myself.
I have 3 tables. Projects,xtra and trial.
Projects is the master, xtra is a detail/master of trial and trial is the detail of xtra.

I am exporting one record of project. When I do that, I am also exporting one record of xtra and one or more records of trial.
I am exporting that into a new database saved on my HD.

Now i am trying to re import theses data. I find what is the last auto inc value from my projects table and update the saved database with this value, then I inport the record. I do not have any duplicate key. I do the same with Xtra and also update the common field PROJECTNO in xtra to reflect the relationship.

The issue is now with Trial as I mau have multiple record. I easily can update the common field that is DATANO. But now, i need to update the primary key that is TRIALNO to values that are not in the database I am imnporting to.

So if I have 3 records in the saved trial database and they were exported with value as 24,25 and 26, I need now to find out the lastautoinc value of the loaded xtra table and update sequentially the values before I insert to.

I amy be complicating myself and it may exist an easy way to export/import records from one database to another. I am doing that to exchange data between users.

Hope that make sense.
Pierrotsc.

 

[Glenn9999]

Okay, I'm still not sure I'm understanding everything that is going on (there's still a lot of missing information). But I gather that you are connecting to two separate databases and trying to make contextual backups of specific record sets in your tables from one to the other? Then the process you are looking at now is trying to reimport the data to an existing database?

And the specific issue you have in doing this is this "lastautoinc" field to make sure the data are consistent? Say your records with 24, 25, & 26. You want those to instead be the next numbers in sequence in the table? For example, the last number in the table you are importing to is 147. You want these records to be 148, 149, 150?

If this is the case, finding the last number in sequence would be simple.

(Untested, and keep in mind I'm not sure I understand your problem completely)

SELECT MAX(lastautoinc)
  FROM TRIAL

The data returned should be the largest "lastautoinc" record.

Your biggest challenge in this import/export task is maintaining referential integrity (*), more than it is getting the task accomplished.

(*) referential integrity is involved with most of the discussion I referred to above about changing primary keys. The main thrust of it is that you maintain all the indicated features of the records when it comes to the database design.


I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[pierrotsc]

Yes, all your assumptions are correct. The issue is not finding the last record #, it is to update the database that i am going to re import if they have multiple records.
I need to know for example on how I can update 3 records. Let's say I know that the lastautoinc is 25. Then i need to be able to update my database primary index TRIALNO with the values of 25, 26 and 27. I know on how to update one record. That is easy. I just do not know on how to update the last 2 records with a SQL statement.
The original exported Database was:
TRIALNO TRIALNAME
3 Test
4 Test 1
5 test 2

Now I want to import that back into a different database. The trail table in this database already have 24 records. So before importing the data, I want to update the database to:
TRIALNO TRIALNAME
25 Test
26 Test 1
27 test 2

Hope now i am making sense.
Thanks for all your patience.
Pierrotsc

 

[Glenn9999]

There's really no auto-incrementing feature in standard SQL. There might be a vendor extension that I don't know of, however.

All you can do is place this within a loop and update each record in turn.

Something like (again untested, but it's semi-pseudocode anyway).

Find highest record in new table.
increment by 1
select old records.
for i := 1 to record_count do
  begin
    fetch first old record.
    update old table set old-value to new-value.
    insert old record into new table
    increment by 1
  end;
close/disconnect.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[pierrotsc]

Thanks. I had a feeling about that. I have been reading sql docs for a few days now.
The thing is that the update command will update the whole table if you do not put any filter. I am not sure what kind of filter you would put except to tell the command to update the current active record. also, I cannot find the sql command to select the NEXT record to go from the first record to the next.
Maybe I should ask on how you insert a table from one database to another without taking a chance of duplicate primary keys.
Or, could I INSERT To all the fields of a record without the Primary key.
Thanks.
Pierrotsc

 

[smays]

I was studying Absolute Database at

http://www.componentace.com

and according to one of the SQL Code samples, you can specify one of the fields (the primary key field) to auto increment:

CREATE TABLE new_table
(NewTableID AutoInc,
SomeOtherField VARCHAR(25)
SomeForeignKeyID INT);

NewTableID in this fictitious table is the Primary Key. I almost always name the Primary Key by the actual table name. By setting the NewTableID to AutoInc, you do not have to specify a value for the NewTableID whenever you execute an INSERT command:

INSERT INTO new_table
(SomeOtherField, SomeForeignKeyID) VALUES
('John Smith', 13);

Notice how the NewTableID is not specified. The database engine will automatically assign a new, unique, incremental value to the NewTableID for you. Depending on what you need to do, you may or may not need to specify the NewTableID when executing an UPDATE command. The following example does not use the NewTableID:

UPDATE new_table
SET SomeForeignKeyID = 27
WHERE SomeOtherField = 'John Smith';

In my opinion, the Primary Key of a table is only useful when JOINing tables. Otherwise, the Primary Key will rarely show up in any of your queries.


I don't know/understand exactly what your entire Client-Database program is supposed to do other than what you have written above. Perhaps it is possible to approach some of these tasks using what I will term as Record Qualifiers (yeah, stupid phrase, I know). The idea is to add fields to your table that state the status of individual records and use those fields to control the outcome of queries.

Imagine a video store Client-Database project. Amongst other tables, you would probably have a table containing the video inventory; a primary key, video name, and check-out status (CheckOutStatus). Neither the primary key nor the video name would change, but the check-out status would be updated every time the video was checked out (CheckedOutStatus = 'O') or returned (CheckedOutStatus = 'I') or on-hold (CheckedOutStatus = 'H') or whatever... If you wanted to know what was available for customers, you would look for CheckedOutStatus = 'I', if you wanted to know everything in inventory, you would ignore the CheckedOutStatus altogether.

Perhaps rather than moving around a pile of records from the main database to some other location and back, you could use an extra field to tell your other queries to ignore the TRIALNAME based on the status of the TRIALNAME.


Steve.

 

[pierrotsc]

Thanks steve. I thought about that but I have some difficulties selecting all the fields except the primary key. The table has indded an autoinc primary key,
If I use:
ABSQuery.SQL.Text := 'INSERT INTO Trial SELECT RedValue FROM "'+db.DatabaseFileName+'".Trial';

I noticed that the redvalue value goes into the first column that is the primary key instead of going into the redvalue field.
I have been reading the docs many time but cannot find out if you can specify or how you can specify where the from field is going to go into.

If you see what I am missing, let me know.
Sincerely,
Pierrotsc

 

[lespaul]

yes you can:

INSERT INTO TRIAL (FieldToBeFilledIn) SELECT RedValue FROM "'+db.DatabaseFileName+'".Trial';

Leslie

 

[Glenn9999]

Yes you can specify specific fields in INSERT. This is in fact one of those reasons that I was talking about in regards to using *. Since * is non-descript, it will act in order of fields (not specifying fields in insert is like that) - into field #1, then #2 and so forth, instead of on the specific fields you want.

lespaul showed you the proper example of how to do it.

I'm waiting for the white paper entitled "Finding Employment in the Era of Occupational Irrelevancy

 

[pierrotsc]

Ok, guys...Firdt let me give a few thanks to you.
Also, i have 150 fields so I guess i will have to concatanate this long string of fields. Looks like it's going to be painful. I was hoping to be able to load everything except the primary key. I did try with 2 fields and it worked. Because, it creates keys aumatically, i am getting huge primary key numbers. Right now i only have 3 records and after inserting a set of 2 records, it gave me a value of 258 and 259.
Do you know if there's a way to reset the increment to start after the last number?
Thanks.
Pierrotsc

 

[whosrdaddy]

the autoinc fields are reset after a "TRUNCATE TABLE your_table" statement. this statement will also delete all records in that table, so beware.

depending on the database, there are ways to ignore the primary key for inserting records. I don't know absolute database, but this is certainly possible in mssql/mysql/oracle and others...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[smays]

The exact reason you have two new primary key values of 258 and 259 for a table with 3 records is literally unknown - perhaps during experimentation you have appended and deleted records on numerous occasions to where the database engine has reached 258 and 259 (I would think this is why you have 258 and 259).


By the way, deleting records has no impact on what number the database engine 'chooses' next. For example, consider the following table and data:

a_table
-------
PrimaryKeyID Field2
.
.
.
257 Some Data
258 Some More Data
259 Even More Data

DELETE FROM a_table WHERE PrimaryKeyID > 258;

INSERT INTO a_table (Field2) VALUES ('Some New Data');

The table now contains the following data:

a_table
-------
PrimaryKeyID Field2
.
.
.
257 Some Data
260 Some New Data


Depending on how you defined your primary key field, SMALLINT, INTEGER, BIGINT, the largest possible value will change. You probably have plenty of room for growth.

Steve.

 

[pierrotsc]

Thanks. Another way, I guess would be to sort the table. If I have index 1,2,3,258,259, could i update it to be 1,2,3,4,5? It looks like I cannot do it with SQL.
What i would like to understand is that, if I have 3 records with index 1,2 and 3. If I delete record #2, then I am left with 2 records with index 1 and 3. Does that mean that index 2 is gone forever?
Thanks for any input on that.
Pierrotsc

 

[pierrotsc]

Thanks...I understand now...