0

How to update the discount_price in DB I need to add value to existing value according to my condition Example: any product has the price from 10 to 30 I have to add 1.5 to it.

product with 15 should be 16.5 for example

I have written this query 

UPDATE products
    SET discount_price = discount_price + 1.5 
    WHERE discount_price >= 10 AND discount_price <= 30;

But I found this error >>

SQL error:

ERROR:  syntax error at or near "SET"
LINE 1: SELECT COUNT(*) AS total FROM (UPDATE products SET discount_...
                                                       ^
In statement:
SELECT COUNT(*) AS total FROM (UPDATE products SET discount_price = discount_price + 1.5 
WHERE discount_price >= 10 AND discount_price <=30) AS sub

enter image description here

Mohammad Kanaan
  • 13
  • 4

1 Answers1

0

I am guessing that you are using Postgres and want to count the number of rows affected by an update. If so, you can use an updatable CTE:

WITH p as (
      UPDATE products
          SET discount_price = discount_price + 1.5 
          WHERE discount_price >= 10 AND discount_price <= 30
          RETURNING *
     )
SELECT COUNT(*)
FROM p;

There is no equivalent syntax in MySQL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I got this ERROR: WITH clause containing a data-modifying statement must be at the top level LINE 1: SELECT COUNT(*) AS total FROM (WITH p as ( ^ In statement: SELECT COUNT(*) AS total FROM (WITH p as ( UPDATE products SET discount_price = discount_price + 1.5 WHERE discount_price >= 10 AND discount_price <= 30 RETURNING * ) SELECT COUNT(*) FROM p) AS sub – Mohammad Kanaan Oct 28 '17 at 12:14
  • @MohammadKanaan . . . You have other code that you are not showing. I don't know where the `select count(*)` is coming from, but it is somewhere. – Gordon Linoff Oct 28 '17 at 12:15
  • Can you Access my laptop via team viewer to see the problem please ? – Mohammad Kanaan Oct 28 '17 at 12:23