Page 1 of 1
Correct SQL Statement to Update Two Columns
Posted: Wed 21 Jun 2006 20:50
by norfintork
Two questions:
Using the following code, I am able to update one column in the table.
1) What is the correct syntax to modify two columns? I try to stick it in before the Where, but no luck.
Datamodule1.Update_Case.sql.Clear;
Datamodule1.Update_Case.sql.add('UPDATE casemaster SET survey_date1 = curdate() where');
Datamodule1.Update_Case.sql.add('inventory.casemaster.autoid =' + CaseMasterid.text);
Datamodule1.Update_Case.Execute;
2) Using the same general theme, what is the syntax to SET a local variable (somedata.text) as opposed to a literal or system variable.
Using:
MySql 5.x
Delphi 7
MyDAC 3.55.0.26
Thanks!
Scott
Posted: Thu 22 Jun 2006 07:12
by Antaeus
1)
You should using something like this:
Code: Select all
UPDATE DEPT SET DName='Dept1', Loc='LONDON' WHERE DeptNo=10;
For more information read
this topic of MySQL Reference Manual.
2)
You should using something like this:
For more information read
this topic of MySQL Reference Manual.
Posted: Thu 22 Jun 2006 15:43
by norfintork
Thanks, I thought the comma was the delimter on #1, but my statement had failed for not putting " " around a value to be posted.
On number two though, I may not have been clear on what I was after:
procedure TForm_Survey.CallFail1Click(Sender: TObject);
begin
Datamodule1.Update_Case.sql.Clear;
Datamodule1.Update_Case.sql.add('UPDATE casemaster SET survey_date1 = curdate(), surveyor1 =' + sys.currentuser);
Datamodule1.Update_Case.sql.add('where inventory.casemaster.autoid =' + CaseMasterid.text);
Datamodule1.Update_Case.Execute;
This statement fails with "Unknown column 'Smith' in filed list. 'Smith' is the text value containted in sys.currentuser - how do I force it to use that as the value to store?
Posted: Thu 22 Jun 2006 22:03
by GEswin
It should be:
Code: Select all
Datamodule1.Update_Case.sql.add('UPDATE casemaster SET survey_date1 = curdate(), surveyor1 =' + QuotedStr(sys.currentuser));
QuotedStr add's Quotes to text (") so SQL knows it's a string.
Best way anyway would be:
Code: Select all
Datamodule1.Update_Case.sql.Clear;
Datamodule1.Update_Case.sql.add('UPDATE casemaster SET survey_date1 = curdate(), surveyor1 = :surveyor1');
Datamodule1.Update_Case.sql.add('where inventory.casemaster.autoid = :autoid' );
Datamodule1.Update_Case.ParamByName('surveyor1').AsString := sys.currentuser;
Datamodule1.Update_Case.ParamByName('autoid').AsString := CaseMasterid.text;
Datamodule1.Update_Case.Execute;
Where second example you only need to set SQL once, and then everytime you need to update, just fill the params and execute query.
Posted: Fri 23 Jun 2006 18:42
by norfintork
Outstanding - what a great forum!
Scott