Mysql Updating with if condition within a Table

Question

I am unable to update 2 fields in a Table using UDPATE AND IF condition. Need help.

I have an invoice table where in If field Nos=0 I need to update field Qty from Capacity and field Nos=1 in the same table.

My sql statement is not working:

UPDATE INVDTLS_draft1 SET `Nos`=1, `Qty`=`Capacity` IF (Nos=0) WHERE id=id

error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (Nos=0) WHERE id=id' at line 1

Suhel Meman · Accepted Answer · 2013-02-18 09:11:42Z

1

try this query, using AND operator

UPDATE INVDTLS_draft1 SET `Nos`=1, `Qty`=`Capacity` WHERE id=id AND Nos=0

answered Feb 18, 2013 at 9:11

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

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

Comments

fthiella · Accepted Answer · 2013-02-18 09:19:02Z

0

Why id=id? I think you need this:

UPDATE INVDTLS_draft1
SET    `Nos`=1, `Qty`=`Capacity`
WHERE  Nos=0

answered Feb 18, 2013 at 9:19

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

1 Comment

user1114409 Over a year ago

Yes you are right, it is a simple statement, not sure why I was thinking about IF condidtion to do this. Thankyou very much.

Bohemian · Accepted Answer · 2013-02-18 09:32:11Z

0

Try this:

UPDATE INVDTLS_draft1 SET 
Nos=1,
Qty = if(nos=0, capacity, Qty)
WHERE id=?

This "does nothing" to qty if nos != 0.

edited Feb 18, 2013 at 9:32

answered Feb 18, 2013 at 9:13

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

2 Comments

user1114409 Over a year ago

This statement is updating all the records in the table, it is not checking for condition

Bohemian Over a year ago

@user1114409 I just copied the where clause from the question. I assumed everyone knew OP meant an app code variable named "id". I've edited the answer to show a placeholder instead

Collectives™ on Stack Overflow

Mysql Updating with if condition within a Table

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related