[UPHPU] Complex SELECT statment in MySQL

Jeffrey Moss jeff at opendbms.com
Mon Nov 15 13:05:51 MST 2004


The problem is you're using an enum when you could be using a "bit" or 
"integer" value. If you read the mysql docs on enums, it advises you not to 
use numbers in an enumerated column.

http://dev.mysql.com/doc/mysql/en/ENUM.html

but one way around this is to use the enum values in string context:

update customer_checklist set daily_call = 0 where ((concat(application) + 
concat(sample_kit) + concat(verification_form)) > 2);

This will automatically cast the string returned by concat to an int value. 
If you don't do this, it will count the index numbers instead of the actual 
translated values.

I'd advise you to re-do your table though.

-Jeff

----- Original Message ----- 
From: "jeffrey neslen" <jneslen at yahoo.com>
To: <uphpu at uphpu.org>
Sent: Monday, November 15, 2004 11:46 AM
Subject: [UPHPU] Complex SELECT statment in MySQL


>I have a situation that calls for me to update rows in
> my db who's certain individual rows add up to a
> certain checksum and was hoping I could get some
> suggestions on how to build my UPDATE statement in
> mysql.
>
> I have a table in my db that contains information
> about certain items that we need to checkoff as we
> send out the items to a customer.  For Example my
> table contains the following set of information:
>
> TABLE 'customer_checklist'(
> customer_id varchar(25) primary key,
> application enum('0','1') default 0,
> sample_kit enum('0','1') default 0,
> verification_form enum('0','1') default 0,
> daily_call enum('0','1') default 0)
>
> Now I am looking to UPDATE all the row's daily_call =
> 0 WHERE the sum of application + sample_kit +
> verification_form are == 2.  In other words if any
> combination of application, sample_kit, and
> verification_form value's add up to 2 then those are
> the rows I want to change the daily_call from 1 to 0.
>
> I know it probably calls for a nested select
> statement, but I can't seem to figure out how I would
> select a sum of three column per row.  Let me know if
> anyone has a solution.
>
> Thanks,
>
> Jeff
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Check out the new Yahoo! Front Page.
> www.yahoo.com
>
>
>
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>
> Sponsored by hostinginferno.com! 




More information about the UPHPU mailing list