CODEWITHSUNDEEP
sqlpostgresql
Anand Vaidya
Anand Vaidya

Reputation: 649

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

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?

Upvotes: 1

Views: 478

Answers (1)

xCloudx8
xCloudx8

Reputation: 721

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

Upvotes: 1

Related Questions