how do i say this to a Query Update

[Guest]

hi all,

This is how i would say it for a single record
UPDATE MyTable
SET MAX(fielda) + 1
WHERE RecordID = RecordB

However, I don't know how to say it to all the RecordIDs in the table:
e.g
RecordID - fielda
RecordA - 1
RecordA - 1
RecordA - 1
RecordB - null
RecordC - null
RecordC - null
RecordC - null

i need fielda for RecordB and RecordC to equal 2 and 3 respectively.

Please advise.

thanks,
ari

thanks,
ari

 

[Guest]

recordID in this case is a foreign key.

ari,

(Sorry about the date appended to the object names, but I have way too
many example "MyTables" floating around.)

CREATE TABLE MyTable_101705_1
(RecordID AUTOINCREMENT
,fielda INTEGER
,CONSTRAINT pk_MyTable_101705_1 PRIMARY KEY (RecordID)
)

Sample Data:

RecordID fielda
1 1
2 1
3 1
4 null
5 null
6 null
7 null


Example Query

UPDATE MyTable_101705_1 AS M1
INNER JOIN
MyTable_101705_1 AS M2
ON M1.RecordID = M2.RecordID + 1
SET M1.fielda = (M2.fielda + 1)
WHERE m1.fielda IS NULL


RecordID fielda
1 1
2 1
3 1
4 2
5 3
6 4
7 5


I realize that this is not exactly what you wanted.

My sample data does not match yours.

However, I did not understand why there were duplicate values in the
RecordID column, the name of which suggests it is the Primary Key of
the table.

It is my hope that this points you in the right direction to solving
your problem (<crosses fingers />).


Sincerely,

Chris O.

 

[Chris2]

hi all,

This is how i would say it for a single record
UPDATE MyTable
SET MAX(fielda) + 1
WHERE RecordID = RecordB

However, I don't know how to say it to all the RecordIDs in the table:
e.g
RecordID - fielda
RecordA - 1
RecordA - 1
RecordA - 1
RecordB - null
RecordC - null
RecordC - null
RecordC - null

i need fielda for RecordB and RecordC to equal 2 and 3 respectively.

Please advise.

thanks,
ari

ari,

(Sorry about the date appended to the object names, but I have way too
many example "MyTables" floating around.)

CREATE TABLE MyTable_101705_1
(RecordID AUTOINCREMENT
,fielda INTEGER
,CONSTRAINT pk_MyTable_101705_1 PRIMARY KEY (RecordID)
)

Sample Data:

RecordID fielda
1 1
2 1
3 1
4 null
5 null
6 null
7 null


Example Query

UPDATE MyTable_101705_1 AS M1
INNER JOIN
MyTable_101705_1 AS M2
ON M1.RecordID = M2.RecordID + 1
SET M1.fielda = (M2.fielda + 1)
WHERE m1.fielda IS NULL


RecordID fielda
1 1
2 1
3 1
4 2
5 3
6 4
7 5


I realize that this is not exactly what you wanted.

My sample data does not match yours.

However, I did not understand why there were duplicate values in the
RecordID column, the name of which suggests it is the Primary Key of
the table.

It is my hope that this points you in the right direction to solving
your problem (<crosses fingers />).


Sincerely,

Chris O.

 

[Chris2]

recordID in this case is a foreign key.

ari,

Hmm, let's see, then.


CREATE TABLE MyTable_101805_1
(MyTableID AUTOINCREMENT
,RecordID TEXT(1)
,fielda INTEGER
,CONSTRAINT pk_MyTable_101805_1 PRIMARY KEY (MyTableID)
)

MyTableID RecordID fielda
1 A 1
2 A 1
3 A 1
4 B null
5 C null
6 C null
7 C null


Query:

UPDATE MyTable_101805_1 AS M1
INNER JOIN
MyTable_101805_1 AS M2
ON M1.MyTableID = (M2.MyTableID + 1)
SET M1.fielda =
SWITCH((M1.RecordID = M2.RecordID), M2.fielda
,(M1.RecordID = CHR(ASC(M2.RecordID) + 1)), M2.fielda +
1)
WHERE m1.fielda IS NULL


Output:

MyTableID RecordID fielda
1 A 1
2 A 1
3 A 1
4 B 2
5 C 3
6 C 3
7 C 3


Ok, this seems to work. I recommend further testing, as I only spent
about five minutes on that.

I hope this meets your needs better than my previous effort.


Sincerely,

Chris O.

 

[Chris2]

Query:

UPDATE MyTable_101805_1 AS M1
INNER JOIN
MyTable_101805_1 AS M2
ON M1.MyTableID = (M2.MyTableID + 1)
SET M1.fielda =
SWITCH((M1.RecordID = M2.RecordID), M2.fielda
,(M1.RecordID = CHR(ASC(M2.RecordID) + 1)), M2.fielda +
1)
WHERE m1.fielda IS NULL



Sincerely,

Chris O.

ari,

The above works, but this would probably be better, as it will work on
columns more than one character in length.

UPDATE MyTable_101805_1 AS M1
INNER JOIN
MyTable_101805_1 AS M2
ON M1.MyTableID = (M2.MyTableID + 1)
SET M1.fielda =
SWITCH((M1.RecordID = M2.RecordID), M2.fielda
,(M1.RecordID <> M2.RecordID), M2.fielda + 1
)
WHERE m1.fielda IS NULL


Sincerely,

Chris O.