Several UPDATE's and more than one condition in one query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
danf_1979
Forum Commoner
Posts: 72
Joined: Sun Feb 20, 2005 9:46 pm

Several UPDATE's and more than one condition in one query?

Post by danf_1979 »

Hi, anyone knows how could I put this in only one query?

Code: Select all

dbsys_query("UPDATE et_system_settings SET setting_value='$website_title' WHERE setting_name='site_name' ");
dbsys_query("UPDATE et_system_settings SET setting_value='$admin_email' WHERE setting_name='emailsender' ");
dbsys_query("UPDATE et_system_settings SET setting_value='$path_to_create/assets/images/' WHERE setting_name='im_plugin_base_dir' ");
dbsys_query("UPDATE et_system_settings SET setting_value='$website_url/assets/images/' WHERE setting_name='im_plugin_base_url' ");
dbsys_query("UPDATE et_system_settings SET setting_value='$path_to_create' WHERE setting_name='filemanager_path' ");
Thanks.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I do not understand why you want to execute all the update statements in a go.
All these statements work on one table and on a set of fields, setting_value and setting_name.
You have different update statement depending on the where clause. I hope there should be one state at any point of time, i.e, you should have one update statement to be executed at any point of time.

If I am wrong, could you explain why you want to do this?

Then coming to the query, you cannot join all these update statements each with different where clause. If you want, I hope you can execute all of them like this...

Code: Select all

query = "UPDATE et_system_settings SET setting_value='$website_title' WHERE setting_name='site_name' ;";
query += "UPDATE et_system_settings SET setting_value='$admin_email' WHERE setting_name='emailsender'; ";
mysql_query(query);
or if you are looking to make one update statement and set value for setting_value field dependant on value of setting_name..use a switch...

Code: Select all

switch($setting_name){
	case "site_name":
		$setting_value = $website_title;
	case "emailsender"
		$setting_value = $admin_email;
}
$query = "update `et_system_settings` SET `setting_value`= '$setting_value' WHERE `setting_name` = '$setting_name'";
mysql_query($query);
Post Reply