CODEWITHSUNDEEP
mysql
SK2017
SK2017

Reputation: 773

Updating column with IF Statement MYSQL

This may be completely impossible in pure MYSQL, but hopefully someone can shed some light.

Basially I am trying to create a schedule that will do a test on a table and update a new table with either Cleared OR Uncleared.

Here is a snapshot of table - testpieces 

TEST_NO RING ROUTE  TYPE_NO
7256    BT1 TSTS    390397
7257    BT1 TCRT    390397
7258    BT1 TCRT    390397
7259    BT1 TCRT    390397
7260    BT1 TCRT    390397
7261    BT1 TCRT    390397
7262    BT1 TCRT    390397
7263    BT1 TCRT    390397
7264    BT1 TCRT    390397
7265    BT1 TCRT    390397
7266    BT1 TCRT    390397
7267    BT1 TCRT    390418
7268    BT1 TCRT    390418
7269    BT1 TCRT    390418
7270    BT1 TCRT    390418
7271    BT1 TCRT    390418
7272    BT1 TCRT    390418
7273    BT1 TCRT    390418
7274    BT1 TCRT    390418
7275    BT1 TCRT    390418
7276    BT1 TCRT    390418
7277    BT1 TCRT    390418

What I am trying to achieve is to do a check on all ROUTE values for each TYPE_NO and if all values of ROUTE are TCRT then this would be true, or if one single value was TSTS and the rest were TCRT then this would be false.

From table 1 you can see that TYPE_NO 390397 is uncleared because not all ROUTE values are TCRT, one is still TSTS. The second type_no 390418 has a ROUTE of all TCRT, this means that Type_no is cleared.

So, I need to update another table called TypeCleared, and have results like the following..

TYPE_NO   TYPE_CLEARED
390397    UNCLEARED
390418    CLEARED

So lets say TypeCleared table has a column full of single Type_no and the Type_Cleared field is blank.

I tried the following - 

 UPDATE TYPECLEARED SET TYPECLEARED.TYPE_CLEARED = "CLEARED" WHERE TYPECLEARED.TYPE_NO = TESTPIECES.TYPE_NO AND ROUTE = "TCRT" ;

   UPDATE TYPECLEARED SET TYPECLEARED.TYPE_CLEARED = "UNCLEARED" WHERE TYPECLEARED.TYPE_NO = TESTPIECES.TYPE_NO AND ROUTE NOT IN ("TCRT");

This sql made the table look like this

 TYPE_NO   TYPE_CLEARED
 390397    UNCLEARED
 390397    CLEARED
 390418    CLEARED

So having failed this way, I thought is there some type of way to create an IF Else statement using user-defined variables.

SET @PIECES =  (SELECT count(test_no) from live_testpiec group by TYPE_NO);

SET @ROUTE =  (SELECT count(ROUTE) from live_testpiec WHERE ROUTING_POS = 'TCRT' group by TYPE_NO);

With the variables I can do an evaluation, if @PIECES = @ROUTE then update that TYPE_NO as Cleared, else Uncleared.

Although, when I try to set these variables, i get the following error - 

Subquery returns more than 1 row

Meaning I can't use group by on the type_no, but this is absolutely crucial to evaluate every group of TYPE_NO.

Is there any other way in doing what I am trying to achieve.. hope this makes sense!

Any questions please ask

Upvotes: 0

Views: 53

Answers (1)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/f4af8/2

UPDATE TypeCleared
INNER JOIN (
 SELECT TYPE_NO, SUM(IF(ROUTE='TSTS',1,0)) as criteria
 FROM testpieces
 GROUP BY TYPE_NO
 ) t
ON  TypeCleared.TYPE_NO = t.TYPE_NO
SET TYPE_CLEARED = IF(t.criteria=0,'CLEARED','UNCLEARED')

Upvotes: 3

Related Questions