1

I have this query below which doesn't seem to work. In 1 blow I wanted to update all rows who's current price is NOT equal to temporary price. I want the column prevprice to copy or be the same as the column currprice.

It does not give any errors, but it never updates the prevprice.

$PreviousPrices = mysqli_query($conn,"UPDATE allproducts WHERE temporaryprice != currprice SET prevprice=currprice");
Improve this question
1
  • After hours more of frustrating trial and error, I notice I can do UPDATE Table Set ColumnA=ColumnB But if I add the WHERE ColumnB!=ColumnC clause that doesn't change ColumnA into ColumnB value Commented May 25, 2016 at 9:04

2 Answers 2

Reset to default
4

SET comes before WHERE

UPDATE allproducts SET prevprice = currprice WHERE temporaryprice != currprice

And, yes, != is valid MySQL:

http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_not-equal

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

12 Comments

I'm pulling all my hair off :D Other queries seem to work but this just doesn't update prevprice when it should $PreviousPrices = mysqli_query($conn,"UPDATE allproducts SET prevprice=temporaryprice WHERE (temporaryprice != currprice)"); I already tried using <> and != and interchanged SET and WHERE vice versa. There's a row in allproducts where temporaryprice is $150.00 and currprice is $200.00 so it should have changed prevprice to $150 but it remains as the old $100
Hmm, not sure. See this SQL Fiddle. On the left, table is built, and the update query is run (notice 3rd row (C)). On the right, a simple select all to show the update. At the bottom, the rows (C is the updated one). sqlfiddle.com/#!9/d6feb7/1
My currprice, prevprice, and temporaryprice are all decimal (10,2), your sample were float types, could that have been the issue?
I think I got it to work, after it worked I wasn't able to continue more testing as I was too tired dealing with this for the whole day.:D But if it continues to work, I can say my mistake was using mysqli_multi_query to insert/update multiple rows but without doing any mysqli_store_result and mysqli_free_result
Yep, seem to be working so far.:) Yeah so for using mysqli_multi_query you must include mysqli_store_result and then mysqli_free_result
|
-1

Whooo... You're in SLQ language here.
!= is not recognised.

<> may be better...
For logic operators see here: http://www.w3schools.com/sql/sql_where.asp

Or read more on the LIKE operator here : http://www.w3schools.com/sql/sql_like.asp

EDIT
And yes, as comments says below your question, there is at least a missing «SET column_name='value'»...

Improve this answer

5 Comments

I thought mysqli can read those as stated in SO question number 3286644. Ok I will try using <>
Interesting. Well, Maybe for mysqli... But keep on sql standards, since mysqli may not be. «i» stands for improved mysql... Which is now deprecated because of it's lack of security. I suggest you to read more on PHP prepared statements which are cross database systems (I read it somewhere...): php.net/manual/en/pdo.prepared-statements.php
Just to clarify... mysqli isn't deprecated... php.net/manual/en/intro.mysqli.php
But mysql_query() is... And then I switched to PHP statements PDO (php.net/manual/en/pdo.prepared-statements.php), time ago. And I think I made the right choice... This is highly opinion at this point, We'll talk more in a few years. Here is the message from the deprecated thing I had when I switched: «Deprecated: mysql_query(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead»
Yeah I find Procedural easier to understand and I am still learning PHP. My plan was to get this working first then will update parts of the PHP code to protect my site. SO question number 3286644 WAS interesting and it seem to show what I was hoping for but never really got it to work that way

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.