Help to write a report
Hi all, I have the following select: select cons_nome as Consultorio, dent_nome as Dentista, pac_nome as Paciente, pac_convenio as Tipo, Pac_matricula as Matricula, concat(consulta_dia, /, consulta_mes, /, consulta_ano) as Data, concat(Consulta_hora, :, consulta_minuto) as Hora, Consulta_procedimento as Proced. from consulta c, dentista d, paciente p, consultorio cc where cc.cons_codigo = c.consulta_codigo_cons and c.consulta_codigo_dentista = d.dent_codigo and c.consulta_codigo_paciente = p.pac_codigo and cons_codigo = 1 and consulta_ano = 2004 and consulta_mes = 09 order by cons_codigo, dent_codigo, consulta_ano, consulta_mes, consulta_dia, Consulta_hora, consulta_minuto into outfile 'teste01.txt'; This select produces the file teste01.txt above: Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/2004 17:20 ORTO Itálica Paulista Dentista 1 MARIBEL ROCIO MELGAR PACHECOS 187635 8/9/2004 17:40 EXO Itálica Paulista Dentista 1 PAULA CRISTINA CAPUCHO S 155181 15/9/2004 80 ORTO Itálica Paulista Dentista 1 JOSEANE DORIA RICARDO S 172261 15/9/2004 820ORTO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 840EXO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 90 ORTO Itálica Paulista Dentista 1 ERICA GAMA PIMENTEL VIEIRA S 199166 15/9/2004 9:40ORTO Itálica Paulista Dentista 2 YASMIN MACHADO SILVAS 194194 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 JAMIL DIAS SILVAS 135453 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 FERNANDA MARTINS FERREIRA N \N 15/9/2004 15:0ORTO Itálica Paulista Dentista 2 ARIANE TELES NASCIMENTO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 IVONEIDE SOUSA DE MENEZES S 148769 15/9/2004 15:40 ORTO Itálica Paulista Dentista 2 JOAO RICARDO DE ABRAHAO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 AMANDA GONCALVES RODRIGUES S 169180 15/9/2004 17:0CLINICA Itálica Paulista Dentista 2 ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/2004 10:0ORTO Itálica Paulista Dentista 2 FABIANA SANCHES OCANHA S 152810 22/9/2004 1:0ORTO and so on... I need that the report have the column headers I put in the select and seems like The following: Consultorio DentistaPaciente Tipo Matricula DataHora Proced. Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/200417:20 ORTO MARIBEL ROCIO MELGAR PACHECO S 187635 8/9/200417:40 EXO PAULA CRISTINA CAPUCHO S 155181 15/9/200480 ORTO JOSEANE DORIA RICARDO S 172261 15/9/2004 820 ORTO RICARDO TERASSI MORAES S \N 15/9/2004840 EXO RICARDO TERASSI MORAES S \N 15/9/200490 ORTO ERICA GAMA PIMENTEL VIEIRAS 199166 15/9/20049:40ORTO Dentista 2 YASMIN MACHADO SILVA S 194194 15/9/20041:0 ORTO JAMIL DIAS SILVA S 135453 15/9/20041:0 ORTO FERNANDA MARTINS FERREIRA N \N15/9/200415:0ORTO ARIANE TELES NASCIMENTO S \N15/9/20041:20ORTO IVONEIDE SOUSA DE MENEZES S 148769 15/9/200415:40 ORTO JOAO RICARDO DE ABRAHAO S \N15/9/2004 1:20ORTO AMANDA GONCALVES RODRIGUES S 169180 15/9/2004 17:0CLINICA ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/200410:0ORTO FABIANA SANCHES OCANHA S 152810 22/9/20041:0 ORTO And this text Itálica should be Itálica Can anybody help me please? Laercio Xisto Braga Cavalcanti Endless Technology Sistemas de Informação Rua Peixoto Gomide 321 Cerqueira César São Paulo SP CEP: 01409-001
Help to write a report
Hi all again, I´m sending my question attached in a .txt file and maybe it will be a little bit easy to understand. Regards, Laercio Xisto Braga Cavalcanti Endless Technology Sistemas de Informação Rua Peixoto Gomide 321 Cerqueira César São Paulo SP CEP: 01409-001 Fone: +5511-3255-3430 mailto: [EMAIL PROTECTED] I have the following select: select cons_nome as Consultorio, dent_nome as Dentista, pac_nome as Paciente, pac_convenio as Tipo, Pac_matricula as Matricula, concat(consulta_dia, /, consulta_mes, /, consulta_ano) as Data, concat(Consulta_hora, :, consulta_minuto) as Hora, Consulta_procedimento as Proced. from consulta c, dentista d, paciente p, consultorio cc where cc.cons_codigo = c.consulta_codigo_cons and c.consulta_codigo_dentista = d.dent_codigo and c.consulta_codigo_paciente = p.pac_codigo and cons_codigo = 1 and consulta_ano = 2004 and consulta_mes = 09 order by cons_codigo, dent_codigo, consulta_ano, consulta_mes, consulta_dia, Consulta_hora, consulta_minuto into outfile 'teste01.txt'; I have 2 problems: 1. The text Itálica in the report should be Itálica 2. The select produces the file teste01.txt above: Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/2004 17:20 ORTO Itálica Paulista Dentista 1 MARIBEL ROCIO MELGAR PACHECO S187635 8/9/2004 17:40 EXO Itálica Paulista Dentista 1 PAULA CRISTINA CAPUCHO S 155181 15/9/2004 80 ORTO Itálica Paulista Dentista 1 JOSEANE DORIA RICARDO S 172261 15/9/2004 820ORTO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 840EXO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 90 ORTO Itálica Paulista Dentista 1 ERICA GAMA PIMENTEL VIEIRA S 199166 15/9/2004 9:40ORTO Itálica Paulista Dentista 2 YASMIN MACHADO SILVAS 194194 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 JAMIL DIAS SILVAS 135453 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 FERNANDA MARTINS FERREIRA N \N 15/9/2004 15:0ORTO Itálica Paulista Dentista 2 ARIANE TELES NASCIMENTO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 IVONEIDE SOUSA DE MENEZES S 148769 15/9/2004 15:40 ORTO Itálica Paulista Dentista 2 JOAO RICARDO DE ABRAHAO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 AMANDA GONCALVES RODRIGUES S 169180 15/9/2004 17:0CLINICA Itálica Paulista Dentista 2 ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/2004 10:0ORTO Itálica Paulista Dentista 2 FABIANA SANCHES OCANHA S 152810 22/9/2004 1:0ORTO and so on... I need that the report have the column headers I put in the select and seems like The following: Consultorio DentistaPacienteTipo Matricula DataHoraProced. Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/200417:20 ORTO MARIBEL ROCIO MELGAR PACHECOS 187635 8/9/200417:40 EXO PAULA CRISTINA CAPUCHO S 155181 15/9/20048:0ORTO JOSEANE DORIA RICARDO S 172261 15/9/20048:20 ORTO RICARDO TERASSI MORAES S \N 15/9/20048:40 EXO RICARDO TERASSI MORAES S \N 15/9/20049:0ORTO ERICA GAMA PIMENTELVIEIRA S 199166 15/9/20049:40 ORTO Dentista 2 YASMIN MACHADO SILVAS 194194 15/9/20041:0ORTO JAMIL DIAS SILVAS 135453 15/9/20041:0ORTO FERNANDA MARTINSFERREIRAN \N 15/9/2004 15:0ORTO ARIANE TELES NASCIMENTO S \N 15/9/20041:20 ORTO IVONEIDE SOUSA DEMENEZESS 148769 15/9/2004 15:40 ORTO JOAO RICARDO DE ABRAHAO S \N 15/9/20041:20 ORTO AMANDA GONCALVESRODRIGUES S 169180 15/9/2004 17:0CLINICA ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/2004 10:0ORTO FABIANA SANCHES OCANHA S
Help with report
Hi all, I have the following select: select cons_nome as Consultorio, dent_nome as Dentista, pac_nome as Paciente, pac_convenio as Tipo, Pac_matricula as Matricula, concat(consulta_dia, /, consulta_mes, /, consulta_ano) as Data, concat(Consulta_hora, :, consulta_minuto) as Hora, Consulta_procedimento as Proced. from consulta c, dentista d, paciente p, consultorio cc where cc.cons_codigo = c.consulta_codigo_cons and c.consulta_codigo_dentista = d.dent_codigo and c.consulta_codigo_paciente = p.pac_codigo and cons_codigo = 1 and consulta_ano = 2004 and consulta_mes = 09 order by cons_codigo, dent_codigo, consulta_ano, consulta_mes, consulta_dia, Consulta_hora, consulta_minuto into outfile 'teste01.txt'; This select produces the file teste01.txt above: Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/2004 17:20 ORTO Itálica Paulista Dentista 1 MARIBEL ROCIO MELGAR PACHECOS 187635 8/9/2004 17:40 EXO Itálica Paulista Dentista 1 PAULA CRISTINA CAPUCHO S 155181 15/9/2004 80 ORTO Itálica Paulista Dentista 1 JOSEANE DORIA RICARDO S 172261 15/9/2004 820ORTO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 840EXO Itálica Paulista Dentista 1 RICARDO TERASSI MORAES S \N 15/9/2004 90 ORTO Itálica Paulista Dentista 1 ERICA GAMA PIMENTEL VIEIRA S 199166 15/9/2004 9:40ORTO Itálica Paulista Dentista 2 YASMIN MACHADO SILVAS 194194 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 JAMIL DIAS SILVAS 135453 15/9/2004 1:0ORTO Itálica Paulista Dentista 2 FERNANDA MARTINS FERREIRA N \N 15/9/2004 15:0ORTO Itálica Paulista Dentista 2 ARIANE TELES NASCIMENTO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 IVONEIDE SOUSA DE MENEZES S 148769 15/9/2004 15:40 ORTO Itálica Paulista Dentista 2 JOAO RICARDO DE ABRAHAO S \N 15/9/2004 1:20 ORTO Itálica Paulista Dentista 2 AMANDA GONCALVES RODRIGUES S 169180 15/9/2004 17:0CLINICA Itálica Paulista Dentista 2 ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/2004 10:0ORTO Itálica Paulista Dentista 2 FABIANA SANCHES OCANHA S 152810 22/9/2004 1:0ORTO and so on... I need that the report have the column headers I put in the select and seems like The following: Consultorio DentistaPaciente Tipo Matricula DataHora Proced. Itálica Paulista Dentista 1 MARIA DA DORES CIRIACO DE SOUZA S 99890 8/9/200417:20 ORTO MARIBEL ROCIO MELGAR PACHECO S 187635 8/9/200417:40 EXO PAULA CRISTINA CAPUCHO S 155181 15/9/200480 ORTO JOSEANE DORIA RICARDO S 172261 15/9/2004 820 ORTO RICARDO TERASSI MORAES S \N 15/9/2004840 EXO RICARDO TERASSI MORAES S \N 15/9/200490 ORTO ERICA GAMA PIMENTEL VIEIRAS 199166 15/9/20049:40ORTO Dentista 2 YASMIN MACHADO SILVA S 194194 15/9/20041:0 ORTO JAMIL DIAS SILVA S 135453 15/9/20041:0 ORTO FERNANDA MARTINS FERREIRA N \N15/9/200415:0ORTO ARIANE TELES NASCIMENTO S \N15/9/20041:20ORTO IVONEIDE SOUSA DE MENEZES S 148769 15/9/200415:40 ORTO JOAO RICARDO DE ABRAHAO S \N15/9/2004 1:20ORTO AMANDA GONCALVES RODRIGUES S 169180 15/9/2004 17:0CLINICA ALINE OLIVIA SILVA OLIVEIRA S 142403 22/9/200410:0ORTO FABIANA SANCHES OCANHA S 152810 22/9/20041:0 ORTO And this text Itálica should be Itálica Can anybody help me please? Laercio Xisto Braga Cavalcanti Endless Technology Sistemas de Informação Rua Peixoto Gomide 321
RE: (if !update then insert) sequence - result Duplicate key :(
Hi, When you use the replace command if the row does not exist it is inserted. MySQL Reference Manual: Section 14.1.6 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted Regards, Laercio. -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: quinta-feira, 30 de setembro de 2004 11:39 To: [EMAIL PROTECTED] Subject: Re: (if !update then insert) sequence - result Duplicate key :( In article [EMAIL PROTECTED], Laercio Xisto Braga Cavalcanti [EMAIL PROTECTED] writes: Hi, To solve this you can use the REPLACE command. The problem is that Aleksandr wants to increment a counter, not set it to some fixed value. How could you use REPLACE for that? -- 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 !update then insert) sequence - result Duplicate key :(
Hi,
To solve this you can use the REPLACE command.
Regards,
Laercio.
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs
Sent: quinta-feira, 30 de setembro de 2004 08:25
To: [EMAIL PROTECTED]
Subject: Re: (if !update then insert) sequence - result Duplicate key :(
In article [EMAIL PROTECTED],
Aleksandr V. Dyomin [EMAIL PROTECTED] writes:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
---
First question: this is good method?
It's good if you expect the UPDATE normally to succeed. Otherwise, you
should first try the INSERT.
Second... My script work on many different hosts with different
hardware, os(only Linux or FreeBSD), and different PHP and MySQL
version. It works fine excepting one thing... Sometime happens errors
like:
MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert
into sometable set keyfield='somekeyvalue', count=1
Why this happens? On some hosts this messageis very rare, but on
others
- so often... I cant understand reason :(
You have a race condition:
* Client 1 tries UPDATE, sees that it fails
* Client 2 tries UPDATE, sees that it fails
* Client 1 does INSERT - okay
* Client 2 does INSERT - duplictae key error
If you think this happens seldom, do the following:
1. Try UPDATE
2. If it fails: try INSERT
3. If it fails due to a duplicate key error: repeat step 1
--
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: Like - Problem
Try the following sintax:
Select * from fruits
where
(fruits.color like '%Green%' or
fruits.color like '%Apple%')
or
(fruits.type like '%Green%' or
fruits.type like '%Apple%');
Regards,
Laercio.
-Original Message-
From: Rui Monteiro [mailto:[EMAIL PROTECTED]
Sent: quarta-feira, 22 de setembro de 2004 04:27
To: [EMAIL PROTECTED]
Subject: Like - Problem
Hello there,
I was wondering how I could make a specific type of search when the string
has more than one word.
Ex.-
String = Green Apple
Select * from fruits
where
(fruits.color like '%Green Apple%'
or fruits.type like '%Green Apple%')
What I thought was breaking the string in 2 words and compares each word
with the fields. The problem is that I can't control how many fields should
be compared.
Also don't know how to compare each word. The following syntax doesn't work:
---
Select * from fruits
where
(fruits.color like in ('%Green%', '%Apple%')
or fruits.type like in ('%Green%', '%Apple%')
---
Any ideas would be very thankful.
Cheer's
Rui Monteiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with tables;
Hi Beauford, Try this: desc spamdata ; Regards, Laercio Cavalcanti. -Original Message- From: Beauford [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 16 de setembro de 2004 01:19 To: [EMAIL PROTECTED] Subject: Problem with tables; Hi, I'm trying to create a table using the commands below, and everything seems to work and the table is there - I just can's access any of the fields. This happens on both my Windows and Linux versions of MySQL. Any help is appreciated. Note, I have 8 other databases that I created with no problem about 2 months ago (on both platforms) - this is the first time I have tried since then. Any help is appreciated Thanks I am in the proper database when I run the commands below... CREATE TABLE spamdata ( id bigint(20) NOT NULL, dates varchar(60) NOT NULL, probe varchar(100) NOT NULL, ip varchar(15) NOT NULL, fqdnvarchar(100) NOT NULL, reason varchar(100) NOT NULL, portvarchar(2) NOT NULL, codevarchar(10) NOT NULL, PRIMARY KEY (idm)); Query OK, 0 rows affected (0.08 sec) *** This tells me nothing happened... When I run 'desc id' (or any of the others) I get the following: mysql desc id; ERROR 1146: Table 'spamtool.id' doesn't exist This shows the table is there though. mysql show tables; ++ | Tables_in_spamtool | ++ | spamdata | ++ 1 rows in set (0.00 sec) -- 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: Is this update possible in MySQL?
Hy Csongor,
You can write this in pure Mysql.
1. First you create a temporary table from your original table events with
group by remoteid and when;
2. Create your new table with the fields value_a, value_b and value_c;
3. Populate your new table from your temporary table with 0 in value_*
fields;
4. Update the value_* fields from the original table.
There may be a simpler way to do this, but I think this is the easyest one.
Regards,
Laercio Cavalcanti
Endless Technology Ltda
-Original Message-
From: Fagyal Csongor [mailto:[EMAIL PROTECTED]
Sent: sexta-feira, 3 de setembro de 2004 11:45
To: [EMAIL PROTECTED]
Subject: Is this update possible in MySQL?
Hi,
I have a table that looks like this:
table events:
id : INT
remoteid: INT
type : enum ('a','b','c')
value: INT
when: DATE
For each 'remoteid' and 'when', there are 3 rows with type=a,b,c
respectively. (Bad table design that is...) I would like to shorten this
table so it will look like this:
id : INT
remoteid: INT
value_a: INT
value_b: INT
value_c: INT
when: DATE
My idea is to dump this table (it is a quite big table with 10M+ rows),
write a perl script to concatenate corresponding 'when'+'remoateid'
fields, and load the resulting file into another table.
Just fo curiosity: can this processing be written in pure MySQL?
Thank you,
- Csongor
--
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: Inserting records from one table to another respecting a condition
Hi, You can use Insert into Suggestion (column 1, column 2, column 3, ..) SELECT partnumber,qty from Sales order by qty desc limit 1,100 Regards, Laercio. -Original Message- From: C.F. Scheidecker Antunes [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 29 de julho de 2004 19:01 To: [EMAIL PROTECTED] Subject: Inserting records from one table to another respecting a condition Hello all, I have two mysql tables. They have a slightly different structure but share comom columns. I need to get the top 100 sold parts ( partnumbers and quantities) from table Sales and insert into table Suggestion if I issue a query : SELECT partnumber,qty from Sales order by qty desc limit 1,100 I would satisfy my need of retrieving the top 100 sold Items from Sales. Then, I need to insert into Suggestion so that I can have an estimate suggestion of purchase. How can I achieve it? Thanks in advance. -- 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: using mysql in commercial software
Hi all, If I write a comercial software that allow my customer at instalation time to select between mysql, interbase or other data base is it legal? Regards, Laercio. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 26 de julho de 2004 15:26 To: Steve Richter Cc: chat. mysql. Subject: Re: using mysql in commercial software Steve Richter wrote: -Original Message- From: Michael Abbott [mailto:[EMAIL PROTECTED] Sent: Monday, July 26, 2004 11:28 AM To: [EMAIL PROTECTED] Subject: RE: using mysql in commercial software This may not be strictly legal, but you could have the end user download MySQL.. to run with your software. exactly! Is Linux distributed under the same type of license as MySql? If I sell software that runs on linux I dont have to give away my code, right? To use my software you first have to install no charge Linux and MySql. Why would that not be permitted? -Steve Because the MySQL license does not allow you to use it free with commercial software that requires MySQL. If you are running commercial software that requires MySQL you must buy a license. -- 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: insertion of multiple rows
You can use replace command instead of insert. It will handle duplicate rows. Regards, Laercio. -Original Message- From: aman [mailto:[EMAIL PROTECTED] Sent: quarta-feira, 21 de julho de 2004 13:01 To: [EMAIL PROTECTED] Subject: RE: insertion of multiple rows I agree with Andrew - this is quicker. And if you area transferring data from one table to the other, you may wanna look at http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html Thanks Aman Raheja http://www.techquotes.com On Wed, 2004-07-21 at 10:51, Andrew Hill wrote: Hi Bruce, You might be interested in the LOAD DATA INFILE syntax. See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html. The command can either be used to replace duplicate records on unique keys, or can ignore them. Cheers, -- Andrew Hill Developer Awarez Ltd. Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7374 -- The information in this email is confidential and is intended solely for the addressee. It may be legally privileged. The contents are not to be disclosed to anyone other than the addressee and access to this email by anyone else is unauthorised. Unauthorised recipients are requested to preserve the confidentiality of this email and to advise the sender immediately of any error in transmission. Any disclosure, copying, distribution or action taken or omitted to be taken in reliance upon the contents of this email by unauthorised recipients is prohibited and may be unlawful. -- -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: 21 July 2004 16:36 To: [EMAIL PROTECTED] Subject: insertion of multiple rows hi... quick question that i can't find an answer to. i have 500-100 rows of data that need to be inserted. i can do it a row at a time. is there a way i can do the inserts all at one time, reducing the hits on the database?? and if i can do the inserts all at once, how do i handle possible duplicates if i have specified in the tbl design that the element should be unique? would the insert bomb or continue??? thanks -bruce -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select, = and like
Hi Karsten,
I'm quite sure you have special characters in the end of the field
T_Instance for T_Instance like VA_SD_STOPFENFUEHRUNG.
Can you display the contents of this field in hex format?
Regards,
Laercio.
-Original Message-
From: Karsten Backhaus
[mailto:[EMAIL PROTECTED]
Sent: quinta-feira, 1 de julho de 2004 06:24
To: [EMAIL PROTECTED]
Subject: select, = and like
Hi,
im running mysql-4.0.18-2, all tables are innodb tables. I found the
following which i dont understand:
one of my tables is named 'PARAMSET_EXT':
mysql explain PARAMSET_EXT;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| PS_Name| varchar(64) | | PRI | | |
| T_Instance | varchar(64) | | PRI | | |
| User | varchar(64) | YES | | NULL| |
| Modified | bigint(80) | | | 0 | |
| D_ID | int(11) | YES | MUL | NULL| |
| Confirmed | tinyint(1) | | | 0 | |
++-+--+-+-+---+
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the following results:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where PS_Name
mysql = 1;
+-+--++
| PS_Name | T_Instance | User |
+-+--++
| 1 | L2_SD_LOOP_DATA1 | vai_l2 |
| 1 | VA_SD_FUCHSPLATTE| vai_l2 |
| 1 | VA_SD_GIESSPULVER| vai_l2 |
| 1 | VA_SD_GIESSROHRWECHSEL | vai_l2 |
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
| 1 | VA_SD_VERTEILEREINBAUTEN | vai_l2 |
+-+--++
6 rows in set (0.00 sec)
mysql
now i send the following select statement and get the following result:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance = VA_SD_STOPFENFUEHRUNG;
Empty set (0.01 sec)
mysql
ok, i expected to get the entry
| 1 | VA_SD_STOPFENFUEHRUNG| vai_l2 |
but, i got an empty set.
(note, that selecting for T_Instances other than VA_SD_STOPFENFUEHRUNG
works!)
however, the following statement works fine:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance like VA_SD_STOPFENFUEHRUNG;
+-+---++
| PS_Name | T_Instance| User |
+-+---++
| 1 | VA_SD_STOPFENFUEHRUNG | vai_l2 |
and now, since the _ character is a wildcard in the like context, i
tried:
mysql select PS_Name, T_Instance, User from PARAMSET_EXT where
mysql T_Instance like VA\_SD\_STOPFENFUEHRUNG;
Empty set (0.00 sec)
mysql
which results in an empty set.
why does selecting for VA_SD_STOPFENFUEHRUNG not work?
Is this some kind of bug or is there anything iam doing wrong?
note, that there are NO special characters in VA_SD_STOPFENFUEHRUNG,
except of the _ which is allowed in this context, isnt it?
regards
Karsten
--
perl -MLWP::Simple -e '$u=http://www.dilbert.com;foreach(split//,
LWP::Simple::get($u)){LWP::Simple::getstore($u$1,dilbert.gif)
if(m#IMG SRC=(/comics/dilbert/archive/images/[^]*)#)}'
--
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: UPDATE...WHERE ... IN (SELECT...)
Hi Markus, You cannot use sub-query in mysql 3.23.49 Regards, Laercio Cavalcanti. -Original Message- From: Markus Källander [mailto:[EMAIL PROTECTED] Sent: quarta-feira, 9 de junho de 2004 13:25 To: [EMAIL PROTECTED] Subject: UPDATE...WHERE ... IN (SELECT...) Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update synopsis error
Saiph, You can use REPLACE command instead of UPDATE. This command will update all rows with the same primary key. Laercio. -Original Message- From: Michael Kruckenberg [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 31 de maio de 2004 18:33 To: saiph Cc: [EMAIL PROTECTED] Subject: Re: update synopsis error What version of MySQL are you using? Subqueries aren't available until 4.1.x. saiph wrote: hi, mysql update table1 set c = (select c2 from table2 where c2 = 'value') where id = 123; give me an ERROR 1064. i.e. create table t1 ( id int primary key, name varchar(20) ); create table t2 ( id int primary key, name varchar(20) ); insert into t1 values(1, 'not right') insert into t2 values(1, 'right') update t1 set name = (select name from t2 where id = 1) where id = 1; how i can update right? -- http://mike.kruckenberg.com | [EMAIL PROTECTED] -- 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: independent database
Hi Marco, You can create one database for each user, and grant access to this database only for this user. Laercio. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 31 de maio de 2004 14:40 To: [EMAIL PROTECTED] Subject: independent database Hi all! I would like that my users can read/write/execute just them database and that they can not read/write/execute other users's database. How shoul I set up? This is because I have to host some web site and I want to give independent access to features. Tnks! marco -- 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: Relationships Question
Hi Richard, Can you make a relationship between COMPOSITIONS and INSTALATIONS? If so, then you make a relationship between COMPSOTIONS_ONSTALATIONS and PERFORMANCES. Laercio. -Original Message- From: Richard Lewis [mailto:[EMAIL PROTECTED] On Behalf Of Richard Lewis Sent: terça-feira, 25 de maio de 2004 09:58 To: [EMAIL PROTECTED] Subject: Relationships Question Dear List, I am working on a database of electronic compositions (and installations etc.) and currently have, amongst others, the following tables: ARTIST(_id_,...) SCORE(_id_,_artist_id_,...) COMPOSITIONS(_id_,_score_id_,...) INSTALLATIONS(_id_,_score_id_,...) PERFORMANCES(_id_,???) The PERFORMANCES table lists dates and locations (etc.) of performances of COMPOSITIONS and INSTALLATIONS in the database. My current entity relationship diagram gives a one-to-many relationship between COMPOSITIONS and PERFORMANCES and between INSTALLATIONS and PERFORMANCES. However, this would involve PERFORMANCES having two foriegn keys (composition_id and installation_id) one of which would be redundant. Whats the best way to design this relationship? Cheers, Richard -- 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: Query question
You can do: Select count(distinct(field)) from table where field 0 Laercio. -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: segunda-feira, 24 de maio de 2004 14:37 To: MySQL List Subject: Query question Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- John C. Nichel KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- 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: Help with update
Hi Michael, Thank you very much. It works good. Regards, Laercio. Laercio Xisto Braga Cavalcanti wrote: Hi all, I'm using mysql 3.23.54 under RedHat9 trying to update a field named pac_fone in a table named paciente with the value of a field named ita_fone from a table named italica as follows: update paciente SET pac_fone= (select ita_fone fromitalica i, paciente p where i.ita_matricula = p.pac_matricula and i.ita_dv= p.pac_dv); and got the following error: mysql update paciente - SET pac_fone= (select ita_fone - fromitalica i, paciente p - where i.ita_matricula = p.pac_matricula - and i.ita_dv= p.pac_dv); ERROR 1064: You have an error in your SQL syntax near 'select ita_fone fromitalica i, paciente p wh' at line 2 Can anybody help me with it? Regards, Laercio. Subselects require mysql 4.1.0 or higher. You could rewrite this as a multiple-table update starting with mysql 4.0.4. With 3.23.54, this will be more difficult. I suppose you could use CREATE...SELECT to make a new table with all the columns from paciente except pac_fone, plus the one column from italica: CREATE TABLE newtable SELECT p.id, p.pac_dv,... i.ita_fone FROM italica i, paciente p WHERE i.ita_matricula = p.pac_matricula AND i.ita_dv = p.pac_dv; and then replace paciente with the new table: RENAME TABLE paciente TO backup, newtable TO paciente; You'd have to recreate any indexes on paciente after that. Perhaps someone will suggest a better way. Michael -- 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]
Help with update
Hi all, I'm using mysql 3.23.54 under RedHat9 trying to update a field named pac_fone in a table named paciente with the value of a field named ita_fone from a table named italica as follows: update paciente SET pac_fone= (select ita_fone fromitalica i, paciente p where i.ita_matricula = p.pac_matricula and i.ita_dv= p.pac_dv); and got the following error: mysql update paciente - SET pac_fone= (select ita_fone - fromitalica i, paciente p - where i.ita_matricula = p.pac_matricula - and i.ita_dv= p.pac_dv); ERROR 1064: You have an error in your SQL syntax near 'select ita_fone fromitalica i, paciente p wh' at line 2 Can anybody help me with it? Regards, Laercio. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing Mysql binary distribution
Hi all, I trying to install and use a MySQL binary distribution doing the folowing steps: Download the file mysql-standard-4.0.20-pc-linux-i686.tar.gz groupadd mysql useradd -g mysql mysql cd /usr/local gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - ln -s full-path-to-mysql-VERSION-OS mysql cd mysql scripts/mysql_install_db chown -R root . chown -R mysql data chgrp -R mysql . bin/mysqld_safe --user=mysql and gotr the following result [EMAIL PROTECTED] local]# ln -s mysql-standard-4.0.20-pc-linux-i686 mysql [EMAIL PROTECTED] local]# cd mysql [EMAIL PROTECTED] mysql]# scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables 040519 15:38:26 Warning: Asked for 196608 thread stack, but got 126976 040519 15:38:26 ./bin/mysqld: Shutdown Complete To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h localhost.localdomain password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com [EMAIL PROTECTED] mysql]# chown -R root . [EMAIL PROTECTED] mysql]# chown -R mysql data [EMAIL PROTECTED] mysql]# chgrp -R mysql . [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql [1] 11928 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040519 15:41:10 mysqld ended [1]+ Donebin/mysqld_safe --user=mysql Can anybody help me??? -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple-table Update
'm using Multiple-table Update with the above sintax:
mysql desc italica;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| ita_empresa | decimal(10,0) | | | 0 | |
| ita_matricula | decimal(10,0) | | PRI | 0 | |
| ita_dv | decimal(1,0) | | PRI | 0 | |
| ita_nome | varchar(50) | YES | | NULL | |
| ita_fone | varchar(19) | YES | | NULL | |
| ita_nascimento | date | YES | | NULL | |
| ita_adesao | date | YES | | NULL | |
| ita_ultpag | date | YES | | NULL | |
++---+--+-+-+---+
8 rows in set (0.00 sec)
mysql desc paciente
- ;
+-+---+--+-+++
| Field | Type | Null | Key | Default | Extra
|
+-+---+--+-+++
| pac_codigo | int(11) | | PRI | NULL |
auto_increment |
| pac_nome | varchar(100) | YES | | NULL |
|
| pac_rg | int(11) | | | 0 |
|
| pac_nascimento | date | | | -00-00 |
|
| pac_sexo | enum('M','F') | | | M |
|
| pac_endereco | varchar(50) | | | |
|
| pac_numero | int(11) | | | 0 |
|
| pac_complemento | varchar(50) | YES | | NULL |
|
| pac_celular | varchar(9) | YES | | NULL |
|
| pac_fone | varchar(9) | YES | | NULL |
|
| pac_fone_com | varchar(9) | YES | | NULL |
|
| pac_convenio | enum('S','N') | | | S |
|
| pac_matricula | int(11) | YES | | NULL |
|
| pac_adesao | date | YES | | NULL |
|
| pac_email | varchar(50) | YES | | NULL |
|
| pac_dv | int(1) | YES | | NULL |
|
| pac_bairro | varchar(50) | YES | | NULL |
|
| pac_cep | varchar(10) | YES | | NULL |
|
| pac_cidade | varchar(50) | YES | | NULL |
|
| pac_ultimopag | date | YES | | NULL |
|
+-+---+--+-+++
20 rows in set (0.00 sec)
update paciente, italica
SET pac_nome = ita_nome,
pac_fone = ita_fone,
pac_nascimento = ita_nascimento,
pac_ultimopag = ita_ultpag
WHERE ita_matricula = pac_matricula
AND ita_dv = pac_dv;
and I got the following error:
ERROR 1064: You have an error in your SQL syntax near ' italica
SET pac_nome = ita_nome,
pac_f' at line 1
What can I do?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
