mysql UPDATE syntax

Hi

i am trying to update a group of values in 1 table all at once.

my sql string is

$sql = "UPDATE job_adverts (Posted_ID, Date_Posted, Job_Title, Job_Code,
Job_Location, Job_Salary, Job_Description, Company_image, Contact_Name,
Contact_Email, Contact_Web, Contact_Company, Contact_Address,
Contact_Telephone, Contact_Fax, company_id, views) SET ('$Posted_ID',
'$Date_Posted', '$Job_Title', '$Job_Code', '$Job_Location', '$Job_Salary',
'$Job_Description', '$Nom_Image', '$Contact_Name', '$Contact_Email',
'$Contact_Web', '$Contact_Company', '$Contact_Address',
'$Contact_Telephone', '$Contact_Fax', '$company_id', '$views') WHERE
ID='$ID'";

but this fails, how should i re-write it? its based on the INSERT INTO code
below which works.

$sql = "INSERT INTO job_adverts (ID, Posted_ID, Date_Posted, Job_Title,
Job_Code, Job_Location, Job_Salary, Job_Description, Company_image,
Contact_Name, Contact_Email, Contact_Web,
Contact_Company, Contact_Address, Contact_Telephone, Contact_Fax,
company_id, views) VALUES ('$ID', '$Posted_ID', '$Date_Posted',
'$Job_Title', '$Job_Code', '$Job_Location', '$Job_Salary',
'$Job_Description',
'$Nom_Image', '$Contact_Name', '$Contact_Email', '$Contact_Web',
'$Contact_Company', '$Contact_Address', '$Contact_Telephone',
'$Contact_Fax', '$company_id', '0')";


Mark

--
--
Obantec Support
www.obantec.net
0845 458 3121
WebHosting and Domains
Nominet UK Member & IPStag Holder
CentralNic Accredited Reseller

Re: mysql UPDATE syntax

Post removed (X-No-Archive: yes)

Re: mysql UPDATE syntax

"Rich" wrote in message
news:e2b2eg02ueb@drn.newsguy.com...
> On Fri, 21 Apr 2006 17:17:25 +0100, Mark D. Smith wrote...

>
>
> The syntax for an UPDATE is a bit different than an INSERT. It should look
> closer to this, replacing "......" with the rest of the updates
>
> UPDATE job_adverts SET
> ID = '$ID',
> Posted_ID = '$Posted_ID',
> Date_Posted = '$Date_Posted',
> ......
> views = '$views'
> WHERE ID = '$ID';
>
>
> Rich
>
>
> --
> NewsGuy.Com 30Gb $9.95 Carry Forward and On Demand Bandwidth
>

Thanks Rich that's fixed it up nicely.

Mark

Re: mysql UPDATE syntax

Mark D. Smith wrote:
> Hi
>
> i am trying to update a group of values in 1 table all at once.
>
> my sql string is
>
> $sql = "UPDATE job_adverts (Posted_ID, Date_Posted, Job_Title, Job_Code,
> Job_Location, Job_Salary, Job_Description, Company_image, Contact_Name,
> Contact_Email, Contact_Web, Contact_Company, Contact_Address,
> Contact_Telephone, Contact_Fax, company_id, views) SET ('$Posted_ID',
> '$Date_Posted', '$Job_Title', '$Job_Code', '$Job_Location', '$Job_Salary',
> '$Job_Description', '$Nom_Image', '$Contact_Name', '$Contact_Email',
> '$Contact_Web', '$Contact_Company', '$Contact_Address',
> '$Contact_Telephone', '$Contact_Fax', '$company_id', '$views') WHERE
> ID='$ID'";

Updates works in a bit different way than insert

$sql = "UPDATE job_adverts SET Posted_ID='$Posted_ID',
Date_Posted='$Date_Posted', Job_Title='$Job_Title', Job_Code='$Job_Code',
Job_Location='$Job_Location', Job_Salary='$Job_Salary',
Job_Description='$Job_Description', Company_image='$Nom_Image',
Contact_Name='$Contact_Name', Contact_Email='$Contact_Email',
Contact_Web='$Contact_Web', Contact_Company='$Contact_Company',
Contact_Address='$Contact_Address', Contact_Telephone='$Contact_Telephone',
Contact_Fax='$Contact_Fax', company_id='$company_id', views='$views' WHERE
ID='$ID'";

For info about UPDATE see: http://dev.mysql.com/doc/refman/5.0/en/update.html


> but this fails, how should i re-write it? its based on the INSERT INTO code
> below which works.

If the ID column is a primary key/unique then this will fail, but if the
column isn't a primary key, then you would have both the updated and the old
entry in the database, which of course ain't a good thing.


//Aho