if condition in update statement 1

[mes123]

I have a table with three fields - stockLevel, backOorder and allocated (there are other fields but these are the inportant ones here).

Is it possible to create a SQL update query that will deduct a qty from the stockLevel if there is sufficient stock and if not deduct as much as possible and put the this amount on allocated and the remainder on backOrder?

Thanks in advance.

 

[DotNetGnat]

case statements??

-DNG

 

[mes123]

Can you give me a clue as to the syntax - trying to find an example to work from at the mo...

 

[DotNetGnat]

does these links help you...

http://dev.mysql.com/doc/mysql/en/control-flow-functions.html

and here is some sample code...

UPDATE Table1
SET foo = CASE
WHEN id=1 THEN 1
WHEN id=2 THEN 2
END
-DNG

 

[mes123]

I've been looking at the examples on the page but can't work how to modify 3 columns depending on the original calculation...

 

[DotNetGnat]

can you show us what query you tried...

then we can work it out...

-DNG

 

[mes123]

Thats the problem, still trying to construct the query... Will post it as soon as done (if it work so others can see solution and if it does not work for help )

 

[DotNetGnat]

let me try to construct on what you stated...

UPDATE Table1
SET myfield=
CASE WHEN qty>100 then stock-100
WHEN qty<100 then allocated
WHEN qty>1000 then backOrder
END

something like that...

-DNG

 

[mes123]

I can describe the logic with a few equations:
S : current stock level
Q : quantity requested
A : quantity allocated
B : quantity on backorder

if (S > Q) then A = Q , S = S - Q
if (Q < S) then A = S , B = B + (Q - S) , S = 0

It's just trying to translate this into an sql update statement with the conditions.

 

[mes123]

sorry - typo 2nd equation sholud be
if (Q > S) then A = S , B = B + (Q - S) , S = 0

 

[DotNetGnat]

you mean this:

UPDATE Table1
SET A=
CASE WHEN S>Q THEN Q ELSE S END,
S=
CASE WHEN S>Q THEN S-Q ELSE 0 END,
B=
CASE WHEN Q<S THEN B+(Q-S) END

not sure though

-DNG

 

[mes123]

Thats it!

My problem was not the logic of the operation, but the simple task of putting it into SQL.

Thanks so much.