POSTGRES: UPDATE multiple rows WHERE columnName has int in given range

Question

I want to execute a query Which has table as mentioned below :

Order_no.   Order_string    Order_Int   Order_status
ABE001       ABE              1                0
ABE002       ABE              2                0
ABE003       ABE              3                0
ABE004       ABE              4                0
ABE005       ABE              5                0
ABE006       ABE              6                0
ABE007       ABE              7                0
ABE008       ABE              8                0
ABE009       ABE              9                0
ABE010       ABE              10               0

I want to query the DB in the following manner:

1> I am getting a request for a range of Example: order_no: ABE003 - ABE007

2> First I want to check whether all the order_no mentioned in range exist or not.

3> Second I need the check if exist the order_status must be 0.

4> If condition 2 and 3 satisfy then UPDATE order_status to 1.

How to query this?

Same data in first 3 columns, just different format... How come? — jarlh
– jarlh, Commented Nov 30, 2017 at 8:09
I have separated the first_column into next two so it could help managing the Db. — Anand Vaidya
– Anand Vaidya, Commented Nov 30, 2017 at 8:12
If this is still early/possible in the design, might want to normalize the table better.. — user2864740
– user2864740, Commented Nov 30, 2017 at 8:16
The general rule is to never store same data twice. I.e. either have Order_no column, or have Order_string and Order_Int columns - but not all three. (You can have a view that returns the extra column(s), if you want.) — jarlh
– jarlh, Commented Nov 30, 2017 at 8:37

xCloudx8 · Accepted Answer · 2017-11-30 08:59:39Z

1

Maybe it's not perfect i cannot test at this time but i would resolve like this:

 UPDATE table
    SET Order_Status =
    CASE
     WHEN EXISTS(SELECT Order_no FROM Table WHERE Order_No BETWEEN "x" AND "y" AND OrderStatus=0) THEN 1
    ELSE  0
    END

-- EDIT BASED ON REQUEST I repeat myself i cannot test if the query is correct but check and try to understand the logic behind and check the postgres documentation.

/*Checking if exists*/
    declare @PackageExists int
    declare @Package_1 nvarchar(45)
    declare @Package_2 nvarchar(45)

    SET @PackageExists = 
    CASE WHEN EXISTS(select * from table where Order_no BETWEEN @Pck_1 And @Pck_2 and Order_Status=0) THEN 1 
    ELSE 0

/*Update table*/
UPDATE table
SET Order_status = 
CASE WHEN @PackageExists=1 THEN 1 ELSE 0
END

edited Nov 30, 2017 at 8:59

answered Nov 30, 2017 at 8:22

xCloudx8

7218 silver badges22 bronze badges

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

5 Comments

Anand Vaidya Over a year ago

What if I just want to check whether all the order_no. specified in request range exist and have order_status=0?

xCloudx8 Over a year ago

Well if you can parameterize the query you can do it dynamically, you can create a first query that populate a variable that is 0 or 1 if all the specified orders fulfill your request. Then based on this variable you can create a second query that updates the column. It's an another approach

Anand Vaidya Over a year ago

Yes, that actually I am searching for. But I am new to PostgreSQL. Can you please provide the syntax ?

xCloudx8 Over a year ago

There you go =)

Anand Vaidya Over a year ago

=) Thank you. You help me a lot.

Collectives™ on Stack Overflow

POSTGRES: UPDATE multiple rows WHERE columnName has int in given range

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related