469,344 Members | 6,547 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

Do update with IF condition... in MySQL

J.P
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?
Jul 20 '05 #1
13 41015
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';
Jul 20 '05 #2
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';
Jul 20 '05 #3
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';
Jul 20 '05 #4
J.P
Aggro <sp**********@yahoo.com> wrote in message news:<i9**************@read3.inet.fi>...
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';


Yeah, well ofcourse. I'm looking for a specific row where them both
match, and if they do match, means that the password is correct for
that specific row (ID). Meaning that the update is allowed and we can
publish whatever it is we want to publish...
Thats the whole idea :-)
Jul 20 '05 #5
J.P
Aggro <sp**********@yahoo.com> wrote in message news:<i9**************@read3.inet.fi>...
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';


Yeah, well ofcourse. I'm looking for a specific row where them both
match, and if they do match, means that the password is correct for
that specific row (ID). Meaning that the update is allowed and we can
publish whatever it is we want to publish...
Thats the whole idea :-)
Jul 20 '05 #6
J.P
Aggro <sp**********@yahoo.com> wrote in message news:<i9**************@read3.inet.fi>...
J.P wrote:
This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'


# This will update row only if the password and id match.
update yourtablename set publish='Y' where id=333 and password='xxx';


Yeah, well ofcourse. I'm looking for a specific row where them both
match, and if they do match, means that the password is correct for
that specific row (ID). Meaning that the update is allowed and we can
publish whatever it is we want to publish...
Thats the whole idea :-)
Jul 20 '05 #7

"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?


So you want to set publish='Y' if there is a password, otherwise set it to
'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) )
);

Regards,

Mike Chirico


Jul 20 '05 #8

"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?


So you want to set publish='Y' if there is a password, otherwise set it to
'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) )
);

Regards,

Mike Chirico


Jul 20 '05 #9

"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?


So you want to set publish='Y' if there is a password, otherwise set it to
'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) )
);

Regards,

Mike Chirico


Jul 20 '05 #10
J.P
"Mike Chirico" <mc******@comcast.net> wrote in message news:<1Y********************@comcast.com>...
"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?


So you want to set publish='Y' if there is a password, otherwise set it to
'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) )
);

Regards,

Mike Chirico

Solution to the problem (SQL):
UPDATE mytable SET publish=IF(password='xxx','Y','N') WHERE id=333

Solution to the problem (in words):
Set publish to 'Y' IF password=xxx ELSE set publish to 'N' WHERE id=333

Thanx.
Jul 20 '05 #11
J.P
"Mike Chirico" <mc******@comcast.net> wrote in message news:<1Y********************@comcast.com>...
"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
Hi!
Anyone knows if it's possible to do an update if, and only if my
condition is TRUE.

Example:

In MYTABLE I have three columns, like this:
ID(INT), PUBLISH(ENUM(Y,N)), PASSWORD(VARCHAR(10))

This is what I want to do:
UPDATE the tablerow with id=333, SET publish='Y' IF password='xxx'

I was trying out the MySQL-expression "IF(expr1,expr2,expr3)" but this
really doesn't do it!

It could work if it was possible to set a SELECT statement in expr1,
returning NULL or EmptyString. Then if NULL DO(execute) expr2 ELSE
DO(execute) expr3

Like this: IF(SELECT * FROM mytable WHERE id=333 AND
PASSWORD='xxx',UPDATE mytable SET publish='N' WHERE id=333, UPDATE
mytable SET publish='Y' WHERE id=333 )

Any ideas?


So you want to set publish='Y' if there is a password, otherwise set it to
'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) )
);

Regards,

Mike Chirico

Solution to the problem (SQL):
UPDATE mytable SET publish=IF(password='xxx','Y','N') WHERE id=333

Solution to the problem (in words):
Set publish to 'Y' IF password=xxx ELSE set publish to 'N' WHERE id=333

Thanx.
Jul 20 '05 #12

"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
"Mike Chirico" <mc******@comcast.net> wrote in message

news:<1Y********************@comcast.com>...
[snip]
So you want to set publish='Y' if there is a password, otherwise set it to 'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) ) );

Regards,

Mike Chirico

Solution to the problem (SQL):
UPDATE mytable SET publish=IF(password='xxx','Y','N') WHERE id=333

Solution to the problem (in words):
Set publish to 'Y' IF password=xxx ELSE set publish to 'N' WHERE id=333

Thanx.


Does it work if password is null on a pre - 4.0 version of MySQL? I think
that may be the only issue. I know > 4.0 works fine with "IF" when an
expression is null; but, there is something in the documentation about
null's in pre 4.0.. on IF conditions.
http://dev.mysql.com/doc/mysql/en/Co...functions.html

However, your "IF" statement is cleaner.

Regards,

Mike Chirico
Jul 20 '05 #13

"J.P" <pa***@propius.net> wrote in message
news:f2**************************@posting.google.c om...
"Mike Chirico" <mc******@comcast.net> wrote in message

news:<1Y********************@comcast.com>...
[snip]
So you want to set publish='Y' if there is a password, otherwise set it to 'N' but do it all in one update.

I believe it can even be done without an if..

update mytable set publish =
concat(substring('Y',1,1-isnull(password)),substring('N',1,isnull(password) ) );

Regards,

Mike Chirico

Solution to the problem (SQL):
UPDATE mytable SET publish=IF(password='xxx','Y','N') WHERE id=333

Solution to the problem (in words):
Set publish to 'Y' IF password=xxx ELSE set publish to 'N' WHERE id=333

Thanx.


Does it work if password is null on a pre - 4.0 version of MySQL? I think
that may be the only issue. I know > 4.0 works fine with "IF" when an
expression is null; but, there is something in the documentation about
null's in pre 4.0.. on IF conditions.
http://dev.mysql.com/doc/mysql/en/Co...functions.html

However, your "IF" statement is cleaner.

Regards,

Mike Chirico
Jul 20 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Westcoast Sheri | last post: by
4 posts views Thread by atabhcy | last post: by
7 posts views Thread by Dave | last post: by
reply views Thread by Fraser Hanson | last post: by
5 posts views Thread by jayson_13 | last post: by
2 posts views Thread by Eduardo Pérez Ureta | last post: by
20 posts views Thread by Mark Harrison | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.