[SOLVED] how subtract 2 from all the grades?

[Delphilearner]

hello

i have 2 tables

Table1

ID
Name
BirthDay

Table2

Math
philosophy
ID

what I want to do is how can i subtract 2 from Table2 (field maht)  for all record groub by ID

and I want to run this code when the program start
i put it in FormShow or FormCreate

[balazsszekely]

Hi Delphilearner,

It's absolutely not clear what are you after(at least for me). Can you be more specific?

regards,
GetMem

[Delphilearner]

thank you for your Reply

what i want to do is subtract 2 form all the fields MAtH in the TABLE2 (which contains students Grades)

for examble

15-2=13
10-2=8
...


I have 2 Tables

TABLE1 contains informations about all the students (name, Birthday ...)
TABLE2 Contains their marks(math,philosophy....)

[balazsszekely]

Do you wish to save back the subtracted value or only display it?

Code: Pascal  [Select][+][-]

1. select (T2.Math - 2) as Math_Sub from Table2 T2
2. where T2.Math is not null /*add other filter here*/

PS: Still not clear what is the link between table1 and table2? Do you have a foreign key?  I would use a Student_ID in table2 which points to ID in Table1

[molly]

PS: Still not clear what is the link between table1 and table2?

In red: grouped by ID. That's the link 

[balazsszekely]

@molly
In red: grouped by ID. That's the link 

Thanks @molly! Feel free to help the OP with further guidance.  Apparently you did understand better the requirement then I did.

[molly]

@getmem:

merely quoting OT:

...
what I want to do is how can i subtract 2 from Table2 (field maht)  for all record groub by ID

Unfortunately i don't have a clue about SQL 

So yes, i've bitten my own arse here but assumed you'd overlooked it just as i initially did. I apologize if i overstepped.

[balazsszekely]

So yes, i've bitten my own arse here but assumed you'd overlooked it just as i initially did. I apologize if i overstepped.

You don't have to apologize, in fact you're right. I did miss the red part somehow, probably because ID is always a unique identifier, never a link to another table, at least this is how it should be.

PS: By the way, it's very hard to help somebody when the requirements are not clear. 

Code: Pascal  [Select][+][-]

1. select T1.ID, T1.Name, T1.BirthDay, (T2.Math - 2) as Math_Sub
2. from Table1 T1
3. left join Table2 T2 on T2.ID = T1.ID

[molly]

@GetMem

PS: By the way, it's very hard to help somebody when the requirements are not clear

Yups, unfortunately i feel that way sometimes as well :-S

Thanks for the solution: new topic required research, and i was attempting to solve it with a clumsy where t1.id = t2.id statement, trying to fit in the subtraction in the select clause

(i believe i found the (forgotten) reason why i never got the hang of things regarding databases  )

Never figured it was possible to do such subtraction in a where clause, so thank you for having taught me something new.

[balazsszekely]

@molly
I'm under the impression that the OP wants to save back the subtracted value to the database. If this is the case we need a completely different approach, like a loop with a query combined with an update script.

[molly]

@GetMem:
Yes, you are correct. If indeed that is what OP had envisioned.

But, pardon my LOL as i quote another requirement OP mentioned:

..and I want to run this code when the program start i put it in FormShow or FormCreate

Which, for sure will make everyone flunk in the end

But apparently, until made clear, we can do as seen fit. In which case i would opt for filling math field with zero's 

[balazsszekely]

Which, for sure will make everyone flunk in the end

Yeah  ! I think we better wait for more info.

[molly]

@molly
I'm under the impression that the OP wants to save back the subtracted value to the database. If this is the case we need a completely different approach, like a loop with a query combined with an update script.

Just for my own clarification/understanding: could something like that also be achieved by using a sql update statement ?

The documentation on that command seems to suggest it is capable of doing so, but might perhaps not work in conjunction with 'syncing' both tables (i have yet to figure out/understand that part).

Scary stuff though, one mistake and you're screwed 
(for the casual reader: always backup before experimenting)

[balazsszekely]

I don't think it's possible to update with a single query, because first you have to select the Math field to subtract 2, then update each row accordingly.

[User137]

It should be possible to use SELECT as the table within UPDATE.

Like there: http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query