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");
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
jay
  • 337
  • 2
  • 9
  • 18
  • 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 – jay May 25 '16 at 09:04

2 Answers2

3

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

Bitwise Creative
  • 4,035
  • 5
  • 27
  • 35
  • 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 – jay May 25 '16 at 05:20
  • 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). http://sqlfiddle.com/#!9/d6feb7/1 – Bitwise Creative May 25 '16 at 06:09
  • My currprice, prevprice, and temporaryprice are all decimal (10,2), your sample were float types, could that have been the issue? – jay May 25 '16 at 07:06
  • No (http://sqlfiddle.com/#!9/d32f56/1), something else is going on. An error maybe? Did you check? Did you run a simple select on your `WHERE` clause to verify the data? `SELECT * FROM allproducts WHERE temporaryprice != currprice` – Bitwise Creative May 25 '16 at 14:29
  • Yes I did, that seem to work, it pulls all the data that matches that description. The actual php page is actually for updating the current database with new info from a CSV file. I coded PHP to read through each rows of a CSV file then INSERT the info to the database using mysqli_multi_query(). Then an IF statement is made for when the multi query was successful it would then do the update where script which I'm having trouble getting to work – jay May 25 '16 at 15:52
  • I tried again, and mysqli_error gives me "Commands out of sync; you can't run this command now". I am on a paid hosting by the way, MariaDB – jay May 25 '16 at 16:08
  • Some light is shed :) This might help: http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now – Bitwise Creative May 25 '16 at 16:09
  • What does this mean? I have multiple mysqli_query in that php page alone as I needed to do multiple changes to the database but I tried adding a mysqli_free_result() after each. When you need to update the database multiple times, but don't need to do anything with the result, can you just reset to do the next update query after the first? – jay May 25 '16 at 16:37
  • I'm not certain. I've never seen this error before. Also, I exclusively use PDO. If you aren't getting an answer from the other SO question I linked to in my last comment, perhaps you should post a new question based on the error, and include your PHP code. Good luck :) – Bitwise Creative May 25 '16 at 16:41
  • 1
    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` – jay May 26 '16 at 01:27
  • Good to hear, jay. I'm glad you got it worked out, and thanks for posting your real solution here. I will upvote it so it remains visible. – Bitwise Creative May 26 '16 at 02:26
  • 1
    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 – jay May 26 '16 at 08:00
0

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'»...

Louys Patrice Bessette
  • 33,375
  • 6
  • 36
  • 64
  • I thought mysqli can read those as stated in SO question number 3286644. Ok I will try using `<>` – jay May 25 '16 at 02:55
  • 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...): http://php.net/manual/en/pdo.prepared-statements.php – Louys Patrice Bessette May 25 '16 at 03:05
  • Just to clarify... `mysqli` isn't deprecated... http://php.net/manual/en/intro.mysqli.php – Bitwise Creative May 25 '16 at 03:15
  • But mysql_query() is... And then I switched to PHP statements PDO (http://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» – Louys Patrice Bessette May 25 '16 at 03:26
  • 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 – jay May 25 '16 at 03:56