mysql update query syntax

Question

I'm trying to use the following query syntax from a php file:

$sql = "UPDATE properties SET properties.ht_hs = 3.5 WHERE properties.oil_data_id = acea.oil_data_id AND (acea.ACEA_A3 = 1 OR acea.ACEA_B3 = 1 OR acea.ACEA_B4 = 1) AND properties.ht_hs < 3.5";

$result = mysql_query($sql) or die(mysql_error());

However, It's not doing what I want. I have two tables in my database, and I need to change the value of some of the records for one column within one of the tables (the ht_hs column/field within the properties table). However,the criteria for when to change that field is dependent upon both tables.

Each motor oil in the database has an id, which is listed in the "oil_data_id" column of each table.

I'm trying to find oils that meet the ACEA A3 or B3 or B4 spec (ie, they have a "1" in that column of the acea table) which also have a value of less than 3.5 in the ht_hs column of the properties table.

If they do, I want to update the value to 3.5.

How can I restructure my query so that it works?

What about splitting your query into multile lines.

Your Common Sense
– Your Common Sense

2011-11-27 19:03:17 +00:00
Commented Nov 27, 2011 at 19:03 — Your Common Sense
– Your Common Sense, Commented Nov 27, 2011 at 19:03

Gigi · Accepted Answer · 2011-11-27 19:05:08Z

1

I think you're looking for something like this:

UPDATE properties
SET properties.ht_hs = 3.5
WHERE properties.oil_data_id in
    (select acea.oil_data_id
     from acea
     where (acea.ACEA_A3 = 1 OR acea.ACEA_B3 = 1 OR acea.ACEA_B4 = 1))
AND properties.ht_hs < 3.5;

answered Nov 27, 2011 at 19:05

Gigi

29.7k32 gold badges117 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Over a year ago

This is the option that I ended up going with. However, I tweaked it a bit. First, I used the WHERE clause within a SELECT statement first to see just what I'd be getting, and, unfortunately, I got alot more than I wanted. Not sure why. So, I ended up doing it as a SELECT statement without the final "AND properties.ht_hs < 3.5" within phpmyadmin. Then, simply selected the records that were incorrect and clicked "change" to manually edit them. Anyway, thanks for the solution. Probably, if I had just run it as UPDATE, it would have worked, but I wanted to be sure I didn't mess it up.

Gigi Over a year ago

I'm glad my answer was useful. Obviously, I don't know anything about the data you have other than what you explained, so verifying that it actually works is always a good idea. As a word of advice, when doing one-time data changes, always do inserts, updates and deletes within a transaction. That way you can check the data, and rollback if it screws up.

ajreal · Accepted Answer · 2011-11-27 19:01:44Z

0

You would need to include table acea in the JOIN like :-

UPDATE properties, acea
SET ...;

See the documentation

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

answered Nov 27, 2011 at 19:01

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Collectives™ on Stack Overflow

mysql update query syntax

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest