Re: if else statement
fre 2006-06-23 klockan 01:52 -0400 skrev Michael Stassen: Thomas Lundström wrote: Not sure what you're aming for here and how your data is structured but why not use a join and alias and fetch all info in one select and then solve what you need in your code? Something in the line of: select t2.col2 from_t2, t3.col2 from_t3 from table1 t1, table2 t2, table3 t3 where t1.id = t2.id and t1.id = t3.id and t1.id = 3 Maybe you can do something like that? That may be a start, but you have the wrong condition on t1.id, and you've left out any mention of t1.col1. Also, explicit JOINs are better than implicit (using commas) JOINs. Well of course you are correct, only probably you've missed the intention. The error is the late-in-the-evening substitution of t1.id = 3 where it should read t1.col1 = 3 of course. Yes, explicit JOINS are always better. These ones are though easier to fiddle with! ;-) Regards, Thomas Lundström -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] #0; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if else statement
Hi Peter,
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
Thanks you for you answers.
Can I put SELECT statements inside the IF statement?
Mysql give error
ERROR 1064: Error in SQL syntax.
Under Control Flow functions of the manual, it says
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending on the
context in which it is used.
But doesn't say what expr is. Examples only show numeric and string functions.
-Original Message-
From: Peter Lauri [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 4:43 PM
To: [EMAIL PROTECTED]; Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: RE: if else statement
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-Original Message-
From: Thomas Lundström [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: Re: if else statement
Not sure what you're aming for here and how your data is structured but why not
use a join and alias and fetch all info in one select and then solve what you
need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3 from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3) Then Select col2 from
table2 where table2.id = 1; Else Select col2 from table3 where
table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1; } else { Select col2
from table3 where table3.id = 1; }
I've read the manual on subqueries but the example don't indicate how
I can do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
Song Ken Vern-E11804 wrote:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Maybe. The first thing to realize, I believe, is that IF() is not a control
flow function, despite what the manual says. IF() is a function whose return
value depends on a condition. It does not allow you to control flow in the
traditional programming sense.
Jørn Dahl-Stamnes wrote:
Maybe:
(SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
and table2.id=1)
UNION
(SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col13
and table3.id=1);
I have not tested it...
That should probably work, though the JOIN conditions make me cringe. Also, I
have no idea why you are using a LEFT-JOIN (and backwards?). I'd have done it
this way:
(SELECT t2.col2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t1.id = 1 AND t1.col1 = 3
)
UNION
(SELECT t3.col2
FROM table1 t1
JOIN table3 t3 ON t1.id = t3.id
WHERE t1.id = 1 AND t1.col1 != 3
);
Thomas Lundström wrote:
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
That may be a start, but you have the wrong condition on t1.id, and you've left
out any mention of t1.col1. Also, explicit JOINs are better than implicit
(using commas) JOINs.
Peter Lauri wrote:
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
I don't believe this will work, as the subqueries will return multiple rows
where one is expected.
Song Ken Vern-E11804 wrote:
Hi Peter,
Thanks you for you answers.
Can I put SELECT statements inside the IF statement?
Mysql give error
ERROR 1064: Error in SQL syntax.
Under Control Flow functions of the manual, it says
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending on the
context in which it is used.
But doesn't say what expr is. Examples only show numeric and string functions.
You've never mentioned your mysql version. 4.1+ is needed for subqueries. In
4.1.15, I find that
IF(condition, (subquery1), (subquery2))
works so long as the subqueries are surrounded by parentheses and always return
exactly one value.
In any case, I don't think subqueries are needed or helpful. If there is at
most one row in table2 and table3 for each id in table1, the following should work:
SELECT IF(t1.col1 = 3, t2.col2, t3.col2)
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
LEFT JOIN table3 t3 ON t1.id = t3.id
WHERE t1.id = 1;
Otherwise, I think the only option is the UNION query above.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if else statement
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
On Wednesday 21 June 2006 11:16, Song Ken Vern-E11804 wrote:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Maybe:
(SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
and table2.id=1)
UNION
(SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col13
and table3.id=1);
I have not tested it...
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if else statement
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-Original Message-
From: Thomas Lundström [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If Else statement
What I have is a shell script that gathers information from a computer, and
I want the script to check to see if there is currently a record that
already exists that has a certain Ethernet(MAC) address. I would also like
to have an ID number for the record that auto-increments so that I can have
an asset number to display on a web page. (Some assets I enter may not have
an Ethernet address because I am creating a database for hardware assets.
Computers, printers, etc., so I need to have an asset ID)
I have ethernet_address set as a Unique Key, and asset_id set as Primary_Key
and to auto-increment.
If I just use replace like below, every time the record is updated, the
asset number that is set to auto-increment is updated. So if it was 5
before, it is now 6. I don't what the asset_id to change of course.
/usr/local/mysql/bin/mysql --user=$username --password=$password
--host=$server cetechnology -e \
REPLACE INTO hardware_assets (ethernet_address, operating_system)\
VALUES \
('$ethernet_address', '$operating_system');
I don't currently have MySql 4.1 running on my server, I have the 3.23.53
that comes with OS X Server 10.2, so if the suggestion you gave me is the
only way (or the best) I will have to upgrade the server. If there is
another way to do the same thing, please let me know.
Mike
From: Egor Egorov [EMAIL PROTECTED]
Date: Mon, 26 Jan 2004 15:40:54 +0200
To: [EMAIL PROTECTED]
Subject: Re: If Else statement
Mike Tuller [EMAIL PROTECTED] wrote:
I am trying write a shell script to check to see if a record exists and if
it does, update information, and if it doesn't insert information. Is there
a way to do an if else statement in MySql?
If you have PRIMARY KEY or UNIQUE index, take a look at REPLACE and INSERT ..
ON DUPLICATE KEY UPDATE statements:
http://www.mysql.com/doc/en/REPLACE.html
http://www.mysql.com/doc/en/INSERT.html
INSERT .. ON DUPLICATE KEY UPDATE is supported since v4.1.0.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If Else statement
Mike Tuller [EMAIL PROTECTED] wrote: I am trying write a shell script to check to see if a record exists and if it does, update information, and if it doesn't insert information. Is there a way to do an if else statement in MySql? If you have PRIMARY KEY or UNIQUE index, take a look at REPLACE and INSERT .. ON DUPLICATE KEY UPDATE statements: http://www.mysql.com/doc/en/REPLACE.html http://www.mysql.com/doc/en/INSERT.html INSERT .. ON DUPLICATE KEY UPDATE is supported since v4.1.0. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If Else statement
I am trying write a shell script to check to see if a record exists and if it does, update information, and if it doesn't insert information. Is there a way to do an if else statement in MySql? I'm stuck on how to do this. I don't want to write it in perl. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IF, THEN ELSE statement
I am trying to update/insert a record into the table depending on whether it exists in the table or not. Something along the lines of... IF (SELECT id FROM myable WHERE id=myid) INSERT INTO mytable(id,name) VALUES (myid, myname) ELSE UPDATE mytable SET name=mynewname WHERE id = myid; Obviously this does not work, but shows what I am trying to achieve in one statement. Is this possible? If so any chance of some example code. Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IF, THEN ELSE statement
See the REPLACE command: http://www.mysql.com/doc/en/REPLACE.html --- I am trying to update/insert a record into the table depending on whether it exists in the table or not. Something along the lines of... IF (SELECT id FROM myable WHERE id=myid) INSERT INTO mytable(id,name) VALUES (myid, myname) ELSE UPDATE mytable SET name=mynewname WHERE id = myid; Obviously this does not work, but shows what I am trying to achieve in one statement. Is this possible? If so any chance of some example code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: IF, THEN ELSE statement
Hi Rob, i think to use REPLACE would do, if there is a unique index on myid: REPACE mytable (myid, myname) VALUES (myid, mynewname); The Manual say in chapter 6.4.8: REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. prosit Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
