Correct SQL Statement to Update Two Columns

[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

[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:

Code: Select all

set @a=1212;
select @a;

For more information read this topic of MySQL Reference Manual.

[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?

[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.

[norfintork]

Outstanding - what a great forum!
Scott