Re: column exists but unknown
Nevermind, the problem was not with the DB :\
On Tue, Mar 3, 2009 at 10:44 AM, Richard Whitney phpmy...@gmail.com wrote:
Hello.
I am running MySQL 5.0.45
and have the following query and error:
SQL query:
SELECT SQL_CALC_FOUND_ROWS CONCAT( 'Edit Space' ) AS '', CONCAT( 'Edit
Status' ) AS '', space_id AS 'Space ID',
TYPE AS 'Ad Type', scheme AS 'Scheme', adman_ad_spaces.status_approved
AS 'Approval Status', adman_ad_spaces.status_paid AS 'Paid Status',
adman_ad_spaces.notes AS 'Notes', start_date AS 'Start Date', end_date
AS 'End Date*', adman_ad_spaces.inserted_on AS 'Created'
FROM adman_ad_spaces, adman_pricing_schemes
INNER JOIN adman_campaigns ON adman_campaigns.campaign_id =
adman_ad_spaces.campaign_id
INNER JOIN adman_users ON adman_users.user_id = adman_campaigns.user_id
WHERE adman_ad_spaces.status = 'Active'
AND adman_ad_spaces.scheme_id = adman_pricing_schemes.scheme_id
AND adman_ad_spaces.status_admin = 'Active'
AND adman_users.name LIKE '%dadasd%'
LIMIT 0 , 10
MySQL said:
#1054 - Unknown column 'adman_ad_spaces.campaign_id' in 'on clause'
part of the structure of adman_ad_spaces:
Field Type Collation Attributes Null Default Extra Action
space_id int(10) UNSIGNED No auto_increment
scheme_id int(10) UNSIGNED No 0
campaign_id int(10) UNSIGNED No 0
type enum('Text', 'Banner', 'Rich', 'Text in List') latin1_swedish_ci No Text
as you can see campaign_id clearly exists in the table
Is my table corrupt?
Any ideas?
Thank you for your help.
Richard
--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559
You come up with the ideas, I come up with the solutions.
--
Richard Whitney
phpmy...@gmail.com
http://phpmydev.com
Ofc. 602-288-5340
Ofc. 877-624-6302
Fax. 480-704-4559
You come up with the ideas, I come up with the solutions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: If exists query.
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and 2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
--- Paul Halliday [EMAIL PROTECTED] wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like
this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events count(*) between 2005-12-10 and
2006-04-14
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web
access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid=1 AND
signature_id1 GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that
address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop
through
top ten')
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to
accomplish.
What is the source of your data?
What is the condition you are testing for?
And what, very specifically, is it that you would like us to help
you with.
--John
I think what you are looking for is the DISTINCT modifier to the
COUNT() aggregate function. That way you count only how many different
values exist in the list, not how many items are in the list.
Count | IP Address| First Seen| Last Seen | Days
SELECT
COUNT(src_ip) AS CNT
, INET_NTOA(src_ip)
, MIN(timestamp) first
, MAX(timestamp) last
, COUNT(DISTINCT DATE(timestamp)) days
FROM event
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC
LIMIT 10
That is close to what you want. There are several ways to convert
timetsamps values into something that can be counted as a unique day
but I think the DATE function will be fast enough.
Shawn Green
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
Paul,
I am doing queries that produce a table that looks
something like this:
Count | IP Address | First Seen | Last
Seen | Days
5000 10.0.0.1 2005-12-10
2006-04-15 50*
6500 10.0.0.2 2006-04-01
2006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10
and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured
within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days
part :) .
snip
You could grab the first day for each src_ip with something like ...
SELECT
COUNT(src_ip) AS CNT,
INET_NTOA(src_ip),
MAX(timestamp),
(SELECT MIN(timestamp) FROM event e2 WHERE e2.src_ip=e1.src_ip) AS
First
FROM event e1
WHERE timestamp '2006-04-24 03:00:00'
AND sid=1
AND signature_id1
GROUP BY src_ip
ORDER BY cnt DESC LIMIT 10
I now want to know; out of all of the days between first seen and
last
seen which days had events on them. I dont want the count(events)
for
eah day, just whether there was an event or not so that I know
between
first seen and last seen what the rate of appearance was.
That's easiest if you maintain a calendar table with one row per day in
the appropriate range. Then you can simply count the rows you get on a
ranged events-calendar join.
PB
-
Paul Halliday wrote:
On 4/23/06, John Hicks [EMAIL PROTECTED] wrote:
Paul Halliday wrote:
I am doing queries that produce a table that looks something like this:
Count | IP Address| First Seen| Last Seen | Days
5000 10.0.0.1 2005-12-102006-04-15 50*
6500 10.0.0.2 2006-04-012006-04-06 3**
*So of the 5000 events "count(*) between 2005-12-10 and 2006-04-14"
the count was distributed within 50 distinct days.
**This time the count is still high and it occured within 3 days
between 2006-04-01 and 2006-04-06.
I just cant figure out how to come up with the days part :).
Well, I guess that is a *bit* more specific, but not much better :)
So I'm guessing that the source of this data is perhaps a web access log
and that you are tracking IP addresses of visitors.
Can we tickle a little more information out of you?
Sorry,
Ok. The data is IDS events. I am not trying to create any new
information I just want to extract information. This information will
be used to relay whether a particular machine has ongoing issues.
For example,
SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
event WHERE timestamp '2006-04-24 03:00:00' AND sid="1" AND
signature_id"1" GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
This will give me the top 10 source addresses for today based on how
many events they have triggered.
If they make the top ten, I want to see when we first saw that address:
SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('loop through
top ten')
I now want to know; out of all of the days between first seen and last
seen which days had events on them. I dont want the count(events) for
eah day, just whether there was an event or not so that I know between
first seen and last seen what the rate of appearance was.
I could do something crufty like this (the row count would be the
answer I am looking for):
SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
DAY;
But that seems like a lot of extra processing.
Thanks and sorry for the confusion.
Ideally, it would be nice to know what task you are trying to accomplish.
What is the source of your data?
What is the "condition" you are testing for?
And what, very specifically, is it that you would like us to help you with.
--John
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
Paul Halliday wrote: I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Could you be a bit more specific? Where do you want to store the count? What do you mean by if there was data on this day? Does the design of the table anticipate no more than one record per day? Does each record correspond to a single day? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If exists query.
I am doing queries that produce a table that looks something like this: Count | IP Address| First Seen| Last Seen | Days 5000 10.0.0.1 2005-12-102006-04-15 50* 6500 10.0.0.2 2006-04-012006-04-06 3** *So of the 5000 events count(*) between 2005-12-10 and 2006-04-14 the count was distributed within 50 distinct days. **This time the count is still high and it occured within 3 days between 2006-04-01 and 2006-04-06. I just cant figure out how to come up with the days part :). On 4/23/06, John Hicks [EMAIL PROTECTED] wrote: Paul Halliday wrote: I am trying to formulate a query that will increment if a condition is true. For example, if I do a select (just let me know if there was data on this day, if so increment count by 1 and check the next day) where timestamp between jan and feb. Could you be a bit more specific? Where do you want to store the count? What do you mean by if there was data on this day? Does the design of the table anticipate no more than one record per day? Does each record correspond to a single day? --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record exists but not found - grrr
Wenca wrote: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results Try it with ...AND `name` LIKE '%Machico%'; I have encountered it once when there were non printable characters in front of or after the text itself. Very frustrating! You can see what extra characters are there when you query SELECT *, HEX(`name`) FROM... This will add a column to the output with the hexadecimal representation of each character in the name. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record exists but not found - grrr
Hi Paul, I'm sure there are no spaces or other blank characters. I run exactly this query (I deleted the row and tried again): INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico'); INSERT INTO tab_p (d_id, name) VALUES (20602, 'Funchal'); SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico'; - no results SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Funchal'; - OK 1 row SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico'; - OK 1 row I really don't understand it. I have this problem with about 3 or 4 words within nearly 5000 that I currently have in the table. Wenca Paul Rhodes wrote: Hi Wenca, Is it possible that you may a space at the beginning or end of the string. Try this to determine whether this is the case: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE '%Machico%' If this works, try deleting and re-inserting the row. I've had a similar situation in the past. Hope this helps, Paul -Original Message- From: Wenca [mailto:[EMAIL PROTECTED] Sent: 27 October 2005 10:56 To: mysql@lists.mysql.com Subject: Record exists but not found - grrr Hi all, I've got a problem that I don't understand and that is driving me mad. I have a table 'tab_p' with this structure: nametype --- p_idmediumint(8) AUTOINCREMENT NOT NULL PRIMARY KEY d_idsmallint(5) NOT NULL namevarchar(50) NOT NULL And unique index on (d_id, name). ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_slovak_ci or utf8_czech_ci And there is a row with (for example) these data: p_idd_idname 953 20602 Machico When I try query: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results So I try to insert the row: INSERT INTO tab_p (d_id, name) VALUES (20602, 'Machico') - Error: #1062 - Duplicate entry '20602-Machico' for key 2 Why!!!??? There are thousands of records in the table an they work fine but then there occures some normal word (even with no special characters) and it behaves like this. Can anyone help me? I'm running MySQL 4.1.11 on Linux. Thanks Wenca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where exists query syntax error?
Barbara Deaton [EMAIL PROTECTED] wrote on 09/02/2005 11:33:19 AM: I need help re-writing a query. When accessing DB2 I can execute the below: delete from DM_KOHLS.clr_plan_price_change_metrics A where A.start_dt= Date( '05/15/2005' ) and exists ( select * from SESSION.delete_table_tmp B where A.clr_plan_sk = B.clr_plan_sk) I modified it to be what I thought was a valid MySQL statement but when I execute it against MySQL I get a syntax error: ERROR: Execute error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists ( select * from delete_table_tmp where clr_plan_price_ch The statement I executed is: delete from clr_plan_price_change_metrics where start_dt= '2005-05-15' and exists ( select * from delete_table_tmp where clr_plan_price_change_metrics. clr_plan_sk = delete_table_tmp.clr_plan_sk) I can successfully execute each piece of the query, but when I combine it with the 'exists' subquery it fails. The examples in the doc show just selects... Exists() so I tried changing it to a select from table and still fails. What is the correct syntax? -Barb. Your version, depending on how old it is, may not support the EXISTS predicate. It may not support the subquery. It may not even support deletes involving more than one table at a time. Please tell us which version you are using so that we can give you a form of your DELETE query compatible with your server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Where exists query syntax error?
I apologize for not providing more information. I am currently using 4.1.8 client and server on windows xp. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 02, 2005 11:48 AM To: Barbara Deaton Cc: mysql@lists.mysql.com Subject: Re: Where exists query syntax error? Barbara Deaton [EMAIL PROTECTED] wrote on 09/02/2005 11:33:19 AM: I need help re-writing a query. When accessing DB2 I can execute the below: delete from DM_KOHLS.clr_plan_price_change_metrics A where A.start_dt= Date( '05/15/2005' ) and exists ( select * from SESSION.delete_table_tmp B where A.clr_plan_sk = B.clr_plan_sk) I modified it to be what I thought was a valid MySQL statement but when I execute it against MySQL I get a syntax error: ERROR: Execute error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists ( select * from delete_table_tmp where clr_plan_price_ch The statement I executed is: delete from clr_plan_price_change_metrics where start_dt= '2005-05-15' and exists ( select * from delete_table_tmp where clr_plan_price_change_metrics. clr_plan_sk = delete_table_tmp.clr_plan_sk) I can successfully execute each piece of the query, but when I combine it with the 'exists' subquery it fails. The examples in the doc show just selects... Exists() so I tried changing it to a select from table and still fails. What is the correct syntax? -Barb. Your version, depending on how old it is, may not support the EXISTS predicate. It may not support the subquery. It may not even support deletes involving more than one table at a time. Please tell us which version you are using so that we can give you a form of your DELETE query compatible with your server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Where exists query syntax error?
Barbara Deaton [EMAIL PROTECTED] wrote on 09/02/2005 01:07:00 PM: I apologize for not providing more information. I am currently using 4.1.8 client and server on windows xp. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 02, 2005 11:48 AM To: Barbara Deaton Cc: mysql@lists.mysql.com Subject: Re: Where exists query syntax error? Barbara Deaton [EMAIL PROTECTED] wrote on 09/02/2005 11:33:19 AM: I need help re-writing a query. When accessing DB2 I can execute the below: delete from DM_KOHLS.clr_plan_price_change_metrics A where A.start_dt= Date( '05/15/2005' ) and exists ( select * from SESSION.delete_table_tmp B where A.clr_plan_sk = B.clr_plan_sk) I modified it to be what I thought was a valid MySQL statement but when I execute it against MySQL I get a syntax error: ERROR: Execute error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists ( select * from delete_table_tmp where clr_plan_price_ch The statement I executed is: delete from clr_plan_price_change_metrics where start_dt= '2005-05-15' and exists ( select * from delete_table_tmp where clr_plan_price_change_metrics. clr_plan_sk = delete_table_tmp.clr_plan_sk) I can successfully execute each piece of the query, but when I combine it with the 'exists' subquery it fails. The examples in the doc show just selects... Exists() so I tried changing it to a select from table and still fails. What is the correct syntax? -Barb. Your version, depending on how old it is, may not support the EXISTS predicate. It may not support the subquery. It may not even support deletes involving more than one table at a time. Please tell us which version you are using so that we can give you a form of your DELETE query compatible with your server. Shawn Green Database Administrator Unimin Corporation - Spruce Pine According to http://dev.mysql.com/doc/mysql/en/delete.html there are 3 valid forms of the DELETE statement. You seem to be trying to use the first one listed DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] delete from clr_plan_price_change_metrics where start_dt= '2005-05-15' and exists ( select * from delete_table_tmp where clr_plan_price_change_metrics.clr_plan_sk = delete_table_tmp.clr_plan_sk ) This is documented to work for SELECT queries but I am not 100% certain it works in DELETE queries. I looked for a bug on this. I seem to remember seeing a change log entry saying something like this was fixed but my boolean query keeps timing out so I can not reference the change log entry or what version it was fixed in. Sorry. One way to actually do what you want to do is to NOT use a subquery and use either one of the multi-table forms. I prefer the first DELETE clr_plan_price_change_metrics FROM clr_plan_price_change_metrics INNER JOIN delete_table_tmp ON clr_plan_price_change_metrics.clr_plan_sk = delete_table_tmp.clr_plan_sk WHERE start_dt= '2005-05-15' Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: if exists
Silently ignore duplicate index inserts... insert IGNORE into PERSON ( NAME ) values ( 'Test') Ed -Original Message- From: Gerhard Gaupp [mailto:[EMAIL PROTECTED] Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if exists
At 12:12 +0200 5/5/04, Gerhard Gaupp wrote: Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Maybe you could just use INSERT IGNORE ... SELECT FROM ... ? That inserts records from one table into another table, ignoring records that duplicate any existing unique key values. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if exists
I think this is half the answer (though you've implied the other half). First, you prevent mysql from accepting duplicate rows by defining a PRIMARY KEY or UNIQUE INDEX on whatever column or columns determine uniqueness. See http://dev.mysql.com/doc/mysql/en/CREATE_INDEX.html for the details. Then, you can INSERT without fear of creating duplicate rows. Adding IGNORE tells mysql not to bother you with an error message if your INSERT is a duplicate. If it is possible that your current info supersedes what is already in the table, you might want to take a look at the manual page for REPLACE, http://dev.mysql.com/doc/mysql/en/REPLACE.html, or, if you use mysql 4.1.x, INSERT...ON DUPLICATE KEY UPDATE... http://dev.mysql.com/doc/mysql/en/INSERT.html. Michael [EMAIL PROTECTED] wrote: Silently ignore duplicate index inserts... insert IGNORE into PERSON ( NAME ) values ( 'Test') Ed -Original Message- From: Gerhard Gaupp [mailto:[EMAIL PROTECTED] Hello I'm looking for something like if not exists( select 1 from PERSON where NAME = 'Test' ) then insert into PERSON ( NAME ) values ( 'Test'); end if; I did it with Sybase Database. I need to get Data from Excel and have to be shure that there is no data more than once in the table. So I have to generate a script with test, if this data is in teh table yet, or not, before inserting. Did You Understand? Sorry for my English Greetings from Germany Gerhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If Exists update else insert
Juan E Suris [EMAIL PROTECTED] wrote:
I need to do something similar to replace, but can't figure out how to do it.
Here's my table:
user varchar(15) PRI
cnt int(11)
I want to increment cnt for user if it exists, else insert a row with cnt=1. I tried
the following, but mysql complains that I can't use the same table in the update and
select:
replace into table set user='someuser', cnt=(select cnt+1 from table where
user='someuser')
Can this be done (in 1 query, of course)?
From v4.1.0 MySQL supports INSERT .. ON DUPLICATE KEY UPDATE and you can write your
query as
INSERT INTO table VALUES('someuser', 1)
ON DUPLICATE KEY UPDATE cnt=cnt+1;
http://www.mysql.com/doc/en/INSERT.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
Re: NOT EXISTS
On Mon, Dec 15, 2003 at 10:59:32AM -, Curley, Thomas wrote: Guys Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id = BATCH.batch_id) You have an error in your SQL syntax near 'EXISTS (select * from TXN where TXN.batch_id = BATCH.batch_id)' at line 1 It's a subselect, and subselects aren't implemented in 4.0 - you need 4.1. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EXISTS
Curley, Thomas [EMAIL PROTECTED] wrote: Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id =3D BATCH.batch_id= ) You have an error in your SQL syntax near 'EXISTS (select * from TXN wher= e TXN.batch_id =3D BATCH.batch_id)' at line 1 NOT EXISTS is supported from v4.1.0. You can rewrite your query using LEFT JOIN: http://www.mysql.com/doc/en/Rewriting_subqueries.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
RE: NOT EXISTS
Many thanks Chris -Original Message- From: Chris Elsworth [mailto:[EMAIL PROTECTED] Sent: 15 December 2003 11:22 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: NOT EXISTS On Mon, Dec 15, 2003 at 10:59:32AM -, Curley, Thomas wrote: Guys Any idea why this query will not work in 4.0.13 select batch_id from BATCH where NOT EXISTS (select 1 from TXN where TXN.batch_id = BATCH.batch_id) You have an error in your SQL syntax near 'EXISTS (select * from TXN where TXN.batch_id = BATCH.batch_id)' at line 1 It's a subselect, and subselects aren't implemented in 4.0 - you need 4.1. -- Chris * This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where exists sql problem
Whoa, I've had this message 3 times now. Is there a problem somewhere? George - Original Message - From: Ben Edwards [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 28, 2002 3:38 PM Subject: where exists sql problem having problems working out sql where exists syntax. Tried query select * from sections s where exists select 0 from pages s where s.page = p.page ) Any ideas, Ben -- [EMAIL PROTECTED]+44 (0)7970 269 522 http://www.subvertise.org -- Altering the Corporate Image http://www.criticaldistribution.org -- Buy Alternative Video Online http://www.videonetwork.org -- Community/Radical Video http://www.spamcop.net/ -- Killing SPAM feels good! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table Exists
Mysqlshow tables; Would be the easy way Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 14, 2001 10:34 AM To: [EMAIL PROTECTED] Subject: Table Exists What is the easiest way to check if a table exists in the current database? I checked the documentation, and couldn't find anything (but maybe I missed it). Glen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table Exists
If you're using PHP, there should be a function for it. You can also use IF NOT EXISTS in your table commands like: CREATE TABLE IF NOT EXISTS, so that you don't overwrite anything. What also might come in handy here is either SHOW TABLES (provided you're in the right database), or DESCRIBE tablename - Jonathan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 14, 2001 8:34 AM To: [EMAIL PROTECTED] Subject: Table Exists What is the easiest way to check if a table exists in the current database? I checked the documentation, and couldn't find anything (but maybe I missed it). Glen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table Exists
If you want to check before creating a table: CREATE TABLE IF NOT EXISTS table_name; If you want to do it before deleting: DELETE TABLE IF EXISTS table_name; [EMAIL PROTECTED] wrote: : Date: Fri, 14 Dec 2001 11:34:26 -0500 : From: [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Subject: Table Exists : : What is the easiest way to check if a table exists in the current : database? I checked the documentation, and couldn't find anything (but : maybe I missed it). : : Glen : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : -- Sherzod Ruzmetov [EMAIL PROTECTED] http://www.UltraCgis.com, Consultant 989.774.6265 ++ | There is nothing wrong with your tools.| | But we can make a better one. | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
