need to reset columnA if columnB is not null

[testing7]

this is probably a simple command but i want to make sure it is right before i run it

 

my ultimate goal is to reset points to 0 in columnA if columnB has a email  address in it. right now columnB is null unless they have input a email address

 

basically they get points in columnA for every click they get on their link and if they have a email address in columnB i am going to pay them and reset their points to 0 but if they dont have an email in columnB their points will stay until next months payout where i will run the command again

 

anyways does this look right?

 

UPDATE table IF columnB=(this is where im confused) SET columnA=0 ;

 

im not sure what i need to put in the email spot because each email is different and im not sure if set =0 is right either.

 

im sure this is easy but im the noob you will have to excuse me.

 

thanks in advance

[fenway]

I can tell you how to make it work, but this isn't a very robust DB design.

[testing7]

what do you mean by that? im just trying to reset a points column to 0 after payments are sent out to my users.

 

 

[fenway]

"resetting" columns is the sign of bad design.  That means you're losing data.

 

 

[testing7]

i just need to know how to do it. we are not a huge site and dont have a large budget for good database design. thanks in advance

[fenway]

i just need to know how to do it. we are not a huge site and dont have a large budget for good database design. thanks in advance

DB design doesn't require a budget -- and poor DB design will be way more expensive in the long run.

 

But I digress -- you haven't told me anything about your tables.

 

I don't know where the e-mail address field is.

[testing7]

table name = users

columnA = points

columnB = paypal

 

if their is a value in paypal other than null i want to reset points to 0 in table name users

[fenway]

update users set points = 0 where paypal is not null

[testing7]

thanks this forum is the best

[The Little Guy]

to help you build a better table here is my suggestion:

 

1. Make a points table

2. have 3 fields: member_id, create_date, points

3. every time you give out points insert a value into the table

4. every time you remove points insert a value into the table (as a negative point value)

5. to get a total sum:

select sum(points) as amount from myTable where member_id = 123;

6. to get a total sum between 2 dates:

select sum(points) as amount from myTable where create_date between '2011-01-01' and '2011-02-01 23:59:59' where member_id = 123;

 

There is a basic way of what you could do.

[testing7]

nice. thats pretty straight forward so i will have to add that. im still learning so every little bit helps. i just wanted to make sure i wasnt going to run a command that was going to screw up anything.