Using if else on update sqlplus

Question

How do i actually use if else with update in sql? I have tried a lot of ways but it still doesnt work.

This is the question:

Add a column called ‘Status’ to a relational table Customer and use one UPDATE statement to fill the column with information about the customer status. Mark ‘Y’ if the customers had placed any orders otherwise ‘X’ will be initialised.

I am stuck at the update part.

UPDATE CUSTOMER
IF(customer.customerID IN (SELECT customerID from invoice))
( set status = 'y' )
else
( set status = 'x' )
endif
where status is null;

you need to use case statement in the update

Srini V
– Srini V

2014-06-05 13:41:54 +00:00
Commented Jun 5, 2014 at 13:41 — Srini V
– Srini V, Commented Jun 5, 2014 at 13:41

TMNT2014 · Accepted Answer · 2014-06-05 14:22:02Z

2

You could use a case statement

UPDATE CUSTOMER
SET Status = CASE WHEN CUSTOMER.CUSTOMERId IN (SELECT customerID from invoice)
    THEN 'y'
    ELSE 'n' END
where status is null;

edited Jun 5, 2014 at 14:22

answered Jun 5, 2014 at 13:42

TMNT2014

2,1401 gold badge12 silver badges13 bronze badges

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

2 Comments

Satwik Nadkarny Over a year ago

My answer is a very simplistic one. Easy to understand for someone who's new to SQL. But this is the correct answer. This is the right way of going about this. This is a perfect example of the use of SQL Case statements and how it helps in condensing SQl queries.

Satwik Nadkarny Over a year ago

+1 Upvoted this. Also, OP should be marked this as the right answer.

ZeRaTuL_jF · Accepted Answer · 2014-06-05 13:49:19Z

2

What you want to do is a case statement because that's the closest you can get to an IF in sql.

update customer
set status
  case
    when exists (select customerID from Customer where CustomerID in (select customerID from invoice) 
    then
        'y'
    else
        'x'
  end
where stats is null

answered Jun 5, 2014 at 13:49

ZeRaTuL_jF

5922 gold badges4 silver badges21 bronze badges

Comments

Satwik Nadkarny · Accepted Answer · 2014-06-05 13:42:29Z

0

DECLARE      @customerID INT
SET          @customerID = (SELECT       CustomerID
                            FROM         Invoice 
                            WHERE        CustomerName = 'some_customer_name')

IF (@customerID = NULL)
BEGIN
   UPDATE     Customer
   SET        Status = 'Y'
   WHERE      CustomerID = @customerID
END
ELSE
BEGIN
   UPDATE     Customer
   SET        Status = 'x'
   WHERE      CustomerID = @customerID
END

answered Jun 5, 2014 at 13:42

Satwik Nadkarny

5,1432 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

Using if else on update sqlplus

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related