String substitution in MYSQL

[Adaikalavan Ramasamy]

I am looking for a string substitution command in MYSQL and was
wondering if anyone can help me. Searching the archives was not
fruitful. Here is an example :

  CREATE TABLE tbl (id INT(2), names VARCHAR(20));
  INSERT INTO tbl VALUES (1, 'aaa');
  INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc');
  SELECT * FROM tbl;
+--+---+
| id   | names |
+--+---+
|1 | aaa   |
|2 | bbb; aaa; ccc |
+--+---+

Now, suppose that I want to change all 'aaa' into 'zzz'. The following
command works for id 1 but not id 2.
  UPDATE tbl SET names='zzz' WHERE names='aaa';

QUESTION : How do I change 'bbb; aaa; ccc' - 'bbb; zzz; ccc' ?

These do not work either :
  UPDATE tbl SET names='zzz' WHERE names like %aaa%;
  UPDATE tbl SET names=%zzz% WHERE names like %aaa%;

I could do this in Perl but prefer to do it in MYSQL for code brevity
and speed. 

Thank you.

Regards, 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: String substitution in MYSQL

[Adaikalavan Ramasamy]

Thank you ! Works like a charm. Here is the successful syntax

  UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz');

I found this link to be quite useful
http://www.jayallen.org/journey/2004/04/how_to_perform_a_mysql_search_and_replace

Regards, Adai.


On Mon, 2004-07-26 at 14:07, Victor Pendleton wrote:
 Try using the REPLACE command.
 
 -Original Message-
 From: Adaikalavan Ramasamy
 To: [EMAIL PROTECTED]
 Sent: 7/26/04 8:04 AM
 Subject: String substitution in MYSQL
 
 I am looking for a string substitution command in MYSQL and was
 wondering if anyone can help me. Searching the archives was not
 fruitful. Here is an example :
 
   CREATE TABLE tbl (id INT(2), names VARCHAR(20));
   INSERT INTO tbl VALUES (1, 'aaa');
   INSERT INTO tbl VALUES (2, 'bbb; aaa; ccc');
   SELECT * FROM tbl;
 +--+---+
 | id   | names |
 +--+---+
 |1 | aaa   |
 |2 | bbb; aaa; ccc |
 +--+---+
 
 Now, suppose that I want to change all 'aaa' into 'zzz'. The following
 command works for id 1 but not id 2.
   UPDATE tbl SET names='zzz' WHERE names='aaa';
 
 QUESTION : How do I change 'bbb; aaa; ccc' - 'bbb; zzz; ccc' ?
 
 These do not work either :
   UPDATE tbl SET names='zzz' WHERE names like %aaa%;
   UPDATE tbl SET names=%zzz% WHERE names like %aaa%;
 
 I could do this in Perl but prefer to do it in MYSQL for code brevity
 and speed. 
 
 Thank you.
 
 Regards, 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

INSERT if record NOT EXISTS

[Adaikalavan Ramasamy]

I am creating a small database that keeps track of users and assigns
them a unique user ID. 

The problem is that sometimes the users might request to be added more
than once (i.e. click on the submit button multiple times). Therefore I
only want to add users if their details (here defined by both firstname,
lastname) are not in the database. Example :

 CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname   
VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
  INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe'); 
  INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
  INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
  SELECT * from tb;
+--+---+--+
| myID | firstname | lastname |
+--+---+--+
|1 | John  | Doe  |
|2 | Jack  | Doe  |
|3 | John  | Smith|
+--+---+--+

I get syntax error with the following :

 INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT  
 EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );

In this case, I want no insert because Jack Doe already exists. Can
anyone help me ?

Thank you.

Regards, Adai.








-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT if record NOT EXISTS

[Adaikalavan Ramasamy]

Thanks Alec ! This works wonderfully.

But I have another related question. How do I write an IF ELSE command
with MYSQL. In this context, I want it to return myID if the record
already exists, otherwise insert into database.

This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');


On Mon, 2004-07-26 at 16:20, [EMAIL PROTECTED] wrote:
 Adaikalavan Ramasamy [EMAIL PROTECTED] wrote on 26/07/2004 
 16:05:23:
 
  I am creating a small database that keeps track of users and assigns
  them a unique user ID. 
  
  The problem is that sometimes the users might request to be added more
  than once (i.e. click on the submit button multiple times). Therefore I
  only want to add users if their details (here defined by both firstname,
  lastname) are not in the database. Example :
  
   CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname 
  VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe'); 
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
  +--+---+--+
  | myID | firstname | lastname |
  +--+---+--+
  |1 | John  | Doe  |
  |2 | Jack  | Doe  |
  |3 | John  | Smith|
  +--+---+--+
  
  I get syntax error with the following :
  
   INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT 
   EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
  
  In this case, I want no insert because Jack Doe already exists. Can
  anyone help me ?
 
 Simply create a UNIQUE index on the fields which you with to be unique. 
 Add into yoyr table cration the line
 UNIQUE (firstname, lastname),
 
 MySQL will then reject any attempt to make that combination non-unique.
 
 Alec
 
 
 
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT if record NOT EXISTS

[Adaikalavan Ramasamy]

This seems more like the solution I want. I am using perl-DBI and when
there is an error (i.e. duplicate insert), the rest of the scrip it not
executed. But this is gives me the following error. What am I doing
wrong ?

mysql desc tb;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| myID  | int(11) |  | PRI | NULL| auto_increment |
| firstname | varchar(10) | YES  | MUL | NULL||
| lastname  | varchar(10) | YES  | | NULL||
+---+-+--+-+-++
3 rows in set (0.00 sec)

mysql INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') ON
DUPLICATE KEY UPDATE lastname = lastname;
ERROR 1064: You have an error in your SQL syntax near 'ON DUPLICATE KEY
UPDATE lastname = lastname' at line 1

Alternatively, I am looking for 'try' equivalent in perl, so that if the
insert is duplicate, the rest of the script is still run. Thank you.

Regards, Adai.


On Mon, 2004-07-26 at 17:20, Michael Dykman wrote:
 from http://dev.mysql.com/doc/mysql/en/INSERT.html:
 14.1.4 INSERT Syntax
 INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
 [INTO] tbl_name [(col_name,...)]
 VALUES ({expr | DEFAULT},...),(...),...
 [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
 
 ...
 If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), 
 and a row is inserted that would cause a duplicate value in a UNIQUE index 
 or PRIMARY KEY, an UPDATE of the old row is performed.
 ...
 end quote
 
 there is no IF NOT EXISTS syntax in INSERT, but you could make use of the 
 ON DUPLICATE KEY mechanism. Assuming you create a unique index on 
 firstname, lastname, your update might read:
 INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') 
 ON DUPLICATE KEY UPDATE lastname = lastname;
 
 which renders the insert neutral.
 
 On Mon, 2004-07-26 at 11:05, Adaikalavan Ramasamy wrote:
  I am creating a small database that keeps track of users and assigns
  them a unique user ID. 
  
  The problem is that sometimes the users might request to be added more
  than once (i.e. click on the submit button multiple times). Therefore I
  only want to add users if their details (here defined by both firstname,
  lastname) are not in the database. Example :
  
   CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname   
  VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe'); 
INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
SELECT * from tb;
  +--+---+--+
  | myID | firstname | lastname |
  +--+---+--+
  |1 | John  | Doe  |
  |2 | Jack  | Doe  |
  |3 | John  | Smith|
  +--+---+--+
  
  I get syntax error with the following :
  
   INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT  
   EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
  
  In this case, I want no insert because Jack Doe already exists. Can
  anyone help me ?
  
  Thank you.
  
  Regards, Adai.
  
  
  
  
  


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT if record NOT EXISTS

[Adaikalavan Ramasamy]

Yes, this does what I want and does not produce an error (which caused
the remaining MYSQL syntax not to be executed). The firstname, lastname
was for example only. In my problem, these are two different identifiers
so I am not worried about multiple dual identifiers.

Thanks to Keith Ivey, Alec Cawley, Gerald Clark and Michael Dykman for
helping with this problem.

I learnt a lot from the list today. Thanks!

Regards, Adai.


On Mon, 2004-07-26 at 18:04, Keith Ivey wrote:
 Adaikalavan Ramasamy wrote:
 
 This naive syntax does not work :
 IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
 lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
 ('Jack', 'Doe');
 
 Assuming you have the unique index on (firstname, lastname), just do
 
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
 
 But how are you planning to handle multiple people named Jack Doe?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Installing MySQL

[Adaikalavan Ramasamy]

Dear all,

Is it possible to install mysql locally on Sun Solaris 8 as I do not have
root permission ?

If so, what is the expected total size on disk and recommended steps
(./configure, make, make --prefix=/my/home/ install : is this sufficient)
?

Any hints or pointers are much appreciated. Thank you very much.

Regards, Adaikalavan Ramasamy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Local installation of mysql

[Adaikalavan Ramasamy]

Dear all,

My system specs are Sun Solaris 8. As I do not have root permission, is it
possible to install mysql locally in my home drive ? I checked the manuals
but there does not seem to answer my question.

If so, what is the expected total size on disk and recommended steps
(./configure, make, make --prefix=/my/home/ install : is this sufficient)
?

Any hints or pointers are much appreciated. Thank you.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]