comparison operators in mysql?

[amedhussaini]

hey all, another question! 

 

i want to update a specific col in a row in a mysql table, ONLY IF two other cols are not equal.

 

1. is there a way to check operators in query language, then update it in one fell swoop? 

2. or should i just query it, pull the values, compare in standard php, then branch off to send update queries?

 

if both are possible, which is fastest?

 

cheers,

amed

 

[haku]

I've wondered the same myself (Im not so great with mysql), but I have found that using a where statement can sometimes work:

"UPDATE table_name SET specific column='something' WHERE id='id' AND (SELECT column_a FROM table_name WHERE id='id' LIMIT 1) != (SELECT column_b FROM table_name WHERE id='id' LIMIT 1)"

 

If 'id' is the same in all 3 spots, then you are working with the same row throughout the whole query. The two inner queries (the SELECT queries) give you the values in columns A and B respectively, and check to see if they are equal. If the are, then no rows will be found by the WHERE statement, and the specific_column will not be updated. If they aren't equal, then the WHERE statement will find that one row, and will update the column accordingly.

 

This is the workaround I have found. There may actually be a way to do if statements in mysql though.

[stuffradio]

Well you could just use basic PHP if staments...

 

$value1 = "one";
$value2 = "two";

if ($value1 !== $value2) {
// Mysql Query here
}

 

You would also use the Where statement as posted above.

 

[amedhussaini]

I've wondered the same myself (Im not so great with mysql), but I have found that using a where statement can sometimes work:

"UPDATE table_name SET specific column='something' WHERE id='id' AND (SELECT column_a FROM table_name WHERE id='id' LIMIT 1) != (SELECT column_b FROM table_name WHERE id='id' LIMIT 1)"

 

If 'id' is the same in all 3 spots, then you are working with the same row throughout the whole query. The two inner queries (the SELECT queries) give you the values in columns A and B respectively, and check to see if they are equal. If the are, then no rows will be found by the WHERE statement, and the specific_column will not be updated. If they aren't equal, then the WHERE statement will find that one row, and will update the column accordingly.

 

This is the workaround I have found. There may actually be a way to do if statements in mysql though.

 

Interesting.  Do you know if the method you presented is faster or slower than just pulling queries to variables, using basic operators to branch then update?

[haku]

It will be faster, as it only requires one query and no php. The other method would require one query to get the value of the two columns, then would require a php conditional to check the values of those two columns, then another query to update the row. The method I choose cuts all that out - it is either done in the one query, or not. Shorter, more compact, and less queries from the database. And that last point is the most important one - you want to use as few queries to the database as you can.