Re: Delete from another table on update.

2012/02/06 11:33 -0400, Paul Halliday Is it possible to wrap a DELETE statement in an ON DUPLICATE KEY UPDATE? Something like: ON DUPLICATE KEY UPDATE host="b1" (DELETE FROM another_table WHERE host="b1") ? No; see http://dev.mysql.com/doc/refman/5.5/en/insert.html Such things

Re: delete all hosts using a wildcard

> > I think what Paul (who wrote a book on MySQL, by the way) was getting at was > that you risk what database folk call "referential integrity issues" if you > mess with *any* data without knowing where else it is used. > [snip]... that was really an important post, excellently written! -Govi

Re: delete all hosts using a wildcard

> From: Tim Dunphy > > ... this is just a test environment so getting rid of those users won't have > any meaningful impact... I think what Paul (who wrote a book on MySQL, by the way) was getting at was that you risk what database folk call "referential integrity issues" if you mess with *an

Re: delete all hosts using a wildcard

;t have any meaningful impact. Also previewing what you > will be deleting by using a select is great advice I intend to use. > > Best > tim > > - Original Message - > From: "Paul DuBois" > To: "Tim Dunphy" > Cc: mysql@lists.mysql.com > Sen

Re: delete all hosts using a wildcard

ice I intend to use. Best tim - Original Message - From: "Paul DuBois" To: "Tim Dunphy" Cc: mysql@lists.mysql.com Sent: Saturday, January 14, 2012 6:46:38 PM Subject: Re: delete all hosts using a wildcard On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > hello l

Re: delete all hosts using a wildcard

On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: > hello list, > > I have a number of hosts that I would like to delete using a wildcard (%) > symbol. > > Here is the query I am using: > > mysql> delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compar

Re: delete syntax

>> >>> well, i am using delete/insert-statements since 10 years to maintain >>> users since you only have to know the tables in the database "mysql" >>> and use "flush privileges" after changes >>> >>> The privileges should be maintained only using the designated commands. >> You cannot rely on

Re: delete syntax

Am 02.12.2011 21:59, schrieb Claudio Nanni: > 2011/12/2 Reindl Harald > >> well, i am using delete/insert-statements since 10 years to maintain >> users since you only have to know the tables in the database "mysql" >> and use "flush privileges" after changes >> >> The privileges should be main

Re: delete syntax

2011/12/2 Reindl Harald > well, i am using delete/insert-statements since 10 years to maintain > users since you only have to know the tables in the database "mysql" > and use "flush privileges" after changes > > The privileges should be maintained only using the designated commands. You cannot

Re: delete syntax

well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database "mysql" and use "flush privileges" after changes DROP USER is the only SINGLE COMMAND as long as you do not use table/column-privileges there are exactly two relevant

Re: delete syntax

DROP USER command is the only command to remove any user and its association from all other tables. Cheers On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald wrote: > ALWAYS > start with "select * from mysql.user where user='mail_admin' and host like > '\%';" > and look what records are affected

Re: delete syntax

ALWAYS start with "select * from mysql.user where user='mail_admin' and host like '\%';" and look what records are affected to make sure the were-statement works as expected and then use "CURSOR UP" and edit the last command to "delete from" not only doing this while unsure with escapes protects

Re: delete syntax

(0.00 sec) > > > sorry for not including enough information last time. > > best > tim > > - Original Message - > From: "Krishna Chandra Prajapati" > To: "Tim Dunphy" > Cc: mysql@lists.mysql.com > Sent: Thursday, December 1, 2011 9:03:46 PM

Re: delete syntax

On 2011-12-02, Tim Dunphy wrote: > > Thanks but I probably should have noted that I only want to delete the > wildcard user. There are other users I would prefer to not delete. > > mysql> select user,host from mysql.user where user='mail_admin'; > ++---+ >| user

Re: delete syntax

luding enough information last time. best tim - Original Message - From: "Krishna Chandra Prajapati" To: "Tim Dunphy" Cc: mysql@lists.mysql.com Sent: Thursday, December 1, 2011 9:03:46 PM Subject: Re: delete syntax delete from mysql.user where user='mail_admin'

Re: delete syntax

delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy wrote: > hello list, > > I am attempting to delete a user from the mysql.user table without > success. > > mysql> delete from mysql.user where user='mail_admin@%'; > Query OK, 0 rows affected (

Re: DELETE CASCADE

On Mon, Apr 5, 2010 at 4:18 PM, Aveek Misra wrote: > I have the following two tables > > CREATE TABLE `cfg_tags` ( > `cluster` varbinary(128) NOT NULL, > `tag` varbinary(128) NOT NULL, > `user` varchar(40) NOT NULL, > PRIMARY KEY (`cluster`,`tag`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > >

Re: Delete table definition without .frm files.

No, if you were to try a create statement, mysql would notice the still-existing data in the InnoDB file. You just have to create a .frm file on the filesystem level, and assign it the correct permissions (on unix, ug=rw and owned by mysql:mysql or something similar). On Mon, Dec 14, 2009 at 2:13

Re: Delete table definition without .frm files.

Thanks Johan, I tried doing this. When I try to delete that empty table it is giving me an error saying Unknow table 'tblename'. I have created empty .frm file at folder level. can I create one from mysql. Thanks in advance. -- Regards, Manasi Save Quoting Johan De Meersman : Heh. Yo

Re: Delete table definition without .frm files.

Heh. You'll need to just create an empty .frm file, and then issue the drop table statement. On Mon, Dec 14, 2009 at 6:11 AM, Manasi Save < manasi.s...@artificialmachines.com> wrote: > Hi All, > > > I really don't know how to elaborate this problem because it is quite > strange- > > > I have dele

RE: DELETE DATA FROM TABLE

19, 2009 1:15 AM To: Gavin Towey Cc: MySQL Subject: Re: DELETE DATA FROM TABLE Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table drop partition But, my concern is "alter table drop partition " on very big t

Re: DELETE DATA FROM TABLE

Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table drop partition But, my concern is "alter table drop partition " on very big table would might take a lot of time. (Although, I haven't tested) Thanks for the immediate r

RE: DELETE DATA FROM TABLE

Assuming you're using either myisam tables, or innodb with file-per-table option turned on, then dropping a whole partition at a time will allow you to reclaim disk space. If you're using innodb with a single tablespace currently, then unfortunately, you would have to export all your data, shut

Re: DELETE DATA FROM TABLE

Krishna Chandra Prajapati wrote: Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records wil

Re: Delete replication on replication

Hi Isart, Thx for the reply. Currently it has been solved. I rolled back and re configure it. What I wonder is what cause the problem, since I did all the same procedures. FYI I didn't make any structure change on my DB and table. TIA. WM On Tue, 2009-06-23 at 21:15 +0100, Isart Montane wrote:

Re: Delete replication on replication

what do yo get when you run "show slave status" on the slave? are you replicating the database you are modifying? Isart Montane On Tue, Jun 23, 2009 at 7:29 AM, sangprabv wrote: > I have configured mysql replication and test to insert some records. > Both nodes synchronized but when I delete 1

Re: DELETE - error_count

2008/10/17 Reyna.Sabina <[EMAIL PROTECTED]>: > Hi, > > Running Environment: > MySQL Server version: 5.0.45 > OS is Red-Hat 64-bit > > The table 'junk' doesn't have a row with id=4. Two tests to trap 'errors' > using DELETE follows: > Perhaps I'm missing something - but a delete matching no

RE: delete query question

Thanks, that did it! > -Original Message- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2008 11:57 AM > To: Jeff Mckeon > Cc: mysql@lists.mysql.com > Subject: Re: delete query question > > Jeff, > > >Table2.ticket = t

Re: delete query question

Jeff, Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created < unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Like this (untested)? DELE

RE: delete query question

> Sent: Tuesday, July 08, 2008 11:27 AM > > To: Jeff Mckeon > > Cc: mysql@lists.mysql.com > > Subject: Re: delete query question > > > > If the tables are InnoDB, you could temporarily set up a foreign key > > relationship between the two, with the 'ON D

RE: delete query question

> -Original Message- > From: Ian Simpson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 08, 2008 11:27 AM > To: Jeff Mckeon > Cc: mysql@lists.mysql.com > Subject: Re: delete query question > > If the tables are InnoDB, you could temporarily set up a foreign ke

Re: delete query question

If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: > I think this is possible but I'm having a total brain fart as to how to > construct the query.. > > Tabl

Re: delete a data from 3 tables

Is there some reason why you don't just delete from the third table first, then the second then the first? Or maybe the reverse order, depending on your key - it's hard to get a good idea of the structure when you just say "1st table is connected with 2nd table by foreign key." Just be sure you d

Re: delete a data from 3 tables

Hi, >I created 3 tables namely authen_tbl,profile_tbl,astro_tbl,in which column name mail as primary key in authen_tbl table , >query: >pid int primary key auto_increment, >foreign key (mail) references authen_tbl(mail) >given in astro_tbl table >and >query >foreign key(pid) references profile_tbl

RE: delete a data from 3 tables

Hi Please let us know the table structures of all. Thanks & Regards, Dilipkumar -Original Message- From: Sebastian Mendel [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2008 1:22 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: delete a data from 3 tables Cha

Re: delete a data from 3 tables

Chandra N schrieb: hi, I would like to know how to delete a data from 3 table which is interconnected with each other by foreign key. i.e 1st table is connected with 2nd table by foreign key and 2nd table is connected with 3rd table with foreign key. please help to solve this problem. What

Re: Delete query question

IN() subqueries in MySQL are badly optimized. It's usually better to use a JOIN, even though it's non-standard: DELETE geno_260k.* FROM geno_260k INNER JOIN ( SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(*)>25 ) AS der USING(ident); Try profili

Re: Delete query question

Thanks baron, I will try this just for test purposes as I already wrote a script, which is slow but not as bad as using IN() Olaf On 9/5/07 3:29 PM, "Baron Schwartz" <[EMAIL PROTECTED]> wrote: > IN() subqueries in MySQL are badly optimized. It's usually better to > use a JOIN, even though it'

Re: Delete query question

Thanks, This seems to work but that IN seems to be really slow... On 9/5/07 9:41 AM, "Justin" <[EMAIL PROTECTED]> wrote: > try > > SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE > a1=0 > GROUP BY ident HAVING count(a1)>25); > > This will give you what you're d

Re: Delete query question

reply inline On 9/5/07, Olaf Stein <[EMAIL PROTECTED]> wrote: > > delete from geno_260k where ident=(select ident from geno_260k where a1=0 > group by ident having count(a1)>25); When a sub query returns more than one row in a where clause, then "=" should be replaced by the "in" . -- Th

Re: Delete query question

try SELECT * from geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident HAVING count(a1)>25); This will give you what you're deleting first.. then if that is good. do DELETE FROM geno_260k WHERE ident IN (SELECT ident FROM geno_260k WHERE a1=0 GROUP BY ident

Re: DELETE query help please?

This should work DELETE Item FROM Item,ItemTag WHERE Item.ProductID =ItemTag.ItemID AND ItemTag.TagID = '168' Mark Kelly wrote: Hi I want to delete from the 'Item' table all the items identified by the folowing query: SELECT Item.ProductID FROM Item, ItemTag WHERE ItemTag.

Re: DELETE query help please?

The following query should work if I understand what you're attempting correctly. Use at your own risk though ;) DELETE FROM Item USING Item, ItemTag WHERE ItemTag.ItemID = Item.ProductID AND ItemTag.TagID = '168'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: DELETE query help please?

Hi. On Thursday 05 July 2007 17:35, you wrote: > > I want to delete from the 'Item' table > > all the items identified by the folowing query: > > If you have MySQL 5+, you can do it using a sub-query: > > DELETE FROM >   Item > WHERE >   ProductID IN ( > SELECT >     Item.ProductID > FROM >     It

RE: DELETE query help please?

> I want to delete from the 'Item' table > all the items identified by the folowing query: If you have MySQL 5+, you can do it using a sub-query: DELETE FROM Item WHERE ProductID IN ( SELECT Item.ProductID FROM Item, ItemTag WHERE ItemTag.TagID = '168' AND ItemTag.Ite

Re: Delete data from databases

Hi Krishna, If you dont have a date column, then on what basis you would be deleting data older than 6 months. As i mentioned earlier, if the no. of records in the table is less then you can use direct delete statment. On 7/5/07, krishna chandra prajapati <[EMAIL PROTECTED]> wrote: Hi All, So

Re: Delete data from databases

Hi Anand, Some of the tables have the date column. what if the table doesn't have the date column. The number of table in the database is more than 200. What else is possible. Is there is any other way to do this except stored procedure Regards, Krishna On 7/5/07, Ananda Kumar <[EMAIL PROTECTED

Re: Delete data from databases

Hi Krishna, You can write a stored proc, where, in the main cursor, select column having unique value for the records to be deleted, For example select item_id from table_name where< sysdate - 180. This cursor will give you value of a column older than 180 days, and inside the main stored

RE: Delete one record, automatically delete other records...

Chris on Monday, August 07, 2006 6:19 PM said: > Foreign keys with an "on delete cascade" should do it. > > http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html Thanks everyone! -- MySQL General Mailing List For list archives: http://lists.mysql.com/m

Re: Delete one record, automatically delete other records...

Chris W. Parker wrote: Hello, I'm not sure if this is possible (or what it's called, and how to search for it) at the db layer or if this has to be done at the application layer... I would like to be able to delete one record in one table and then automatically (without making an extra call to t

RE: Delete one record, automatically delete other records...

You want cascade deletion via foreign keys. http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html is one such place to learn more. Tim > -Original Message- > From: Chris W. Parker [mailto:[EMAIL PROTECTED] > Sent: Monday, August 07, 2006 9:11 PM > To: mysql@lists.mysql.com

Re: Delete one record, automatically delete other records...

On 8/7/06, Chris W. Parker <[EMAIL PROTECTED]> wrote: Hello, I'm not sure if this is possible (or what it's called, and how to search for it) at the db layer or if this has to be done at the application layer... I would like to be able to delete one record in one table and then automatically (wi

Re: DELETE based on JOIN

Jesse wrote: I need to delete some records based on a JOIN relation. The following works in Microsoft SQL Server, and the syntax appears to be correct for MySQL, but it reports "syntax..near 'DELETE FROM ConvInvDet FROM ConvInvDet H...". What's the proper way to format this for MySQL? DELETE

Re: Delete Duplicates

Rich <[EMAIL PROTECTED]> wrote on 03/30/2006 09:11:56 PM: > Hi there. Any quick way of killing duplicate records? > > Cheers > Yes. Some ways involve subqueries, others temporary tables. What version are you on? What are your table definition(s) (use SHOW CREATE TABLE to dump the defs)? How

Re: Delete Duplicates

Subquries will help you . --Praj On Thu, 30 Mar 2006 21:11:56 -0500 Rich <[EMAIL PROTECTED]> wrote: > Hi there. Any quick way of killing duplicate records? > > Cheers > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[

Re: Delete Duplicates

Rich wrote: Hi there. Any quick way of killing duplicate records? Cheers Subqueries probably. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.co

Re: Delete query problem

Dusan. - Original Message - From: "Tomas Rasek" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 21, 2005 11:12 AM Subject: Re: Delete query problem What about DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . T.R.

Re: Delete query problem

On 21/12/2005, Dušan Pavlica wrote: > Hello, > I have master-detail tables and I would like to delete in one > statement master record and all detail records but not every master > record has details. MySQL versions 4.1.10 and higher. Could someone > help me, please, to create such a query? >

Re: Delete query problem

What about DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . T.R. Dušan Pavlica napsal(a): Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master rec

Re: Delete all but the newest 100 records?

> I'd love to get the offset working if possible. Since offset is not in the supported syntax for deletes (see http://dev.mysql.com/doc/refman/5.0/en/delete.html), I think you'll have some trouble with that. You could do something like this: SELECT @theDeadline := `timestamp` FROM `table` ORDER B

Re: Delete all but the newest 100 records?

> On Nov 7, 2005, at 11:04 AM, Scott Noyes wrote: > >> delete from x where ID not in (select ID from x order by timestamp desc >> limit >> 100); > > It's a good suggestion, I'm just shying away from it because it seems > more resource intensive than using an offset, and my ISP is super anal >

Re: Delete all but the newest 100 records?

Interesting thought. I just tried it with and even 999 - same error! I'm sure that 999 records is not too large for MySQL... :) :) On Nov 7, 2005, at 4:52 PM, Joseph Cochran wrote: The quoted line is 99 but you're using , which I presume is too big for the system to reco

Re: Delete all but the newest 100 records?

On Nov 7, 2005, at 11:04 AM, Scott Noyes wrote: delete from x where ID not in (select ID from x order by timestamp desc limit 100); It's a good suggestion, I'm just shying away from it because it seems more resource intensive than using an offset, and my ISP is super anal about resources

Re: Delete all but the newest 100 records?

On Nov 6, 2005, at 6:05 PM, Jeremy Cole wrote: DELETE FROM tbl ORDER BY ts DESC LIMIT 100, 99; That's exactly needed: I was not aware of the OFFSET option. So I tried this, which appears correct to me: DELETE FROM table WHERE field='somevalue' ORDER BY timestamp DESC LIMIT 100,

Re: Delete all but the newest 100 records?

Hi Brian, Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 Yahoo employs MySQL geeks? Always wondered what db that big index runs on... :) Do note that Yahoo! is a lot more than a search engine. :) The "big index" is not running in MySQL, but a whole lot more is. And yes, we emp

Re: Delete all but the newest 100 records?

On Nov 6, 2005, at 6:05 PM, Jeremy Cole wrote: Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 Yahoo employs MySQL geeks? Always wondered what db that big index runs on... :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lis

Re: Delete all but the newest 100 records?

Hi Brian, I'd like to delete all but the newest 100 records from a table. I have a timestamp field. I can't figure out how to do this in a single SQL statement - can anyone help? Thanks! This should work: DELETE FROM tbl ORDER BY ts DESC LIMIT 100, 99; Note that ORDER BY and LIMIT are

Re: Delete all but the newest 100 records?

The problem is the most recent 100 records won't be sequential. There are records with many different identifiers, in random order mixed with other records that I don't want deleted, and each time I do this I'm going to be limiting each subset of identified records to only the most recent 1

Re: Delete all but the newest 100 records?

söndagen den 6 november 2005 16:15 skrev Brian Dunning: > I'd like to delete all but the newest 100 records from a table. I > have a timestamp field. I can't figure out how to do this in a single > SQL statement - can anyone help? Thanks! I'd try something like this: delete from x where ID not in

Re: Delete all but the newest 100 records?

If you have auto_incremnt id in the table you can do the following: delete from table a where id > start_id and id =< end_id Or you can delete by timestamp. delete from table a where date between 'start_date' and 'end_date'. On 11/6/05, Brian Dunning <[EMAIL PROTECTED]> wrote: > > I'd like to d

Re: DELETE Trigger

Hello Scott, > How do I create a trigger in MySQL similiar to the trigger created for mssql: > > CREATE TRIGGER DEL_DE ON KRONOS > AFTER DELETE > AS > DELETE FROM > DE_Metrics > WHERE > KID=( > SELECT > ID > FROM > DELETED > ); > > I already read online mysql TRIGGER, CREATE TRIGGER and USING TRIG

Re: delete with an offset?

Dobromir Velev wrote: delete from where to_uid=1 and read_timestamp>0 and timestamp<(select timestamp from messages where to_uid=1 and read_timestamp>0 order by timestamp desc limit 100,1) HTH Thanks for your answer, I guess i'll have to do with two queries, but it looked like such and ide

Re: delete with an offset?

I don't think it is possible at least not with one query. You can limit the numbers of record to delete, but at least until 4.1 there is no offset. Probably you can do it with a subselect like this delete from where to_uid=1 and read_timestamp>0 and timestamp<(select timestamp from messages wh

Re: DELETE syntax

http://dev.mysql.com/doc/mysql/en/delete.html ... Multiple-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition] Or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[

RE: delete, where, and subselects...

Hi, You can't delete selected rows from the same table in mysql. Just create a temp table containing the select result. And delete from table A where existe select ... from temp_table; Mathias -Original Message- From: Jason Pyeron [mailto:[EMAIL PROTECTED] Sent: vendredi 2 septembre 20

Re: Delete without Overhead on a MEMORY.

[EMAIL PROTECTED] wrote: We are getting lots of Overhead in our MEMORY table when we delete rows that are to old. So How do we delete from the table an not consumer MEMORY that we want later? Your last 3 questions lack any details of what you're trying to do, or what problem you're experiencing

Re: DELETE FROM statement seems not to use my index...

Ruben Edna wrote: Here is create table def: CREATE TABLE `tblmainupdateinfo` ( `MainID` int(11) unsigned NOT NULL default '0', `ClientID` int(11) unsigned NOT NULL default '0', `UpdateInfo` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`MainID`,`ClientID`), KEY `ClientID` (`ClientID

Re: Delete duplicate records

> > > > > > > delete from mytable where record_no=? limit ? > > Setting the first parameter to the record number and the second to count > - 1. > Ah "limit", yes that should help. However, I do not know each individual nos for these "record_no" Can I have sth that will dynamically delete those rec

Re: Delete duplicate records

Manish wrote: I have got few duplicate record in my DB as follows.- Let's say record_no is the unique id of duplicate records. So I have fields ID, record_no , X and Y. (ID is a dif filed and is a primary key) To find duplicates I do following - select record_no,count(record_no) from mytable group

Re: Delete data from more than one table

Jerry Swanson wrote: I can delete data for one table with no problem: delete from table; I need to delete data for more than one table. I tried to run this query: delete from account, survey; //But the query crashes. Any ideas how to delete data for more than one query. TH multi-table delete s

Re: Delete data from more than one table

if your schema can be set up to use pk/fk constraints, you can add an on delete cascade statement. if not, then its maybe safer to delete table by table anyway. Jerry Swanson wrote: I can delete data for one table with no problem: delete from table; I need to delete data for more than one table.

Re: Delete duplicate entry

Hi Gerald, > try ALTER IGNORE TABLE. Thank you very much. I should have checked the manual first. http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table. If IGNORE i

Re: Delete duplicate entry

Daniel Kasak wrote: gerald_clark wrote: Batara Kesuma wrote: Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datet

Re: Delete duplicate entry

Eldo Skaria wrote: Hi, I think the query has to be considering the count rather than the sum. the query can be like this: select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from group by pkfield1[,pkfield2[,pkfield3[,]]] having count(1) > 1 Here u can add n-number of feilds which u want ma

Re: Delete duplicate entry

Hi, I think the query has to be considering the count rather than the sum. the query can be like this: select pkfield1[,pkfield2[,pkfield3[,]]], count(1) from group by pkfield1[,pkfield2[,pkfield3[,]]] having count(1) > 1 Here u can add n-number of feilds which u want make PK. Reg, El

Re: Delete duplicate entry

gerald_clark wrote: Batara Kesuma wrote: Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datetime` datetime default

Re: Delete duplicate entry

Batara Kesuma wrote: Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datetime` datetime default NULL, KEY `idx_1`

Re: DELETE FROM mysql.user?

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

Re: DELETE FROM mysql.user?

At 9:54 -0600 9/2/04, Mark C. Stafford wrote: Should I care that USAGE still shows up after all rights have been revoked? It feels like a potential security hole... USAGE *means* "no privileges", that's why it shows up when all privs have been revoked. It does mean that the account can be used to c

Re: DELETE FROM mysql.user?

"USAGE" is a synonym for "no privileges." Yes, it's kind of a confusing term which implies "this user can use the system" when in fact the situation is exactly the opposite. http://dev.mysql.com/doc/mysql/en/User_resources.html Cheers, --V Mark C. Stafford wrote: Should I care that USAGE still

Re: DELETE doesn't delete?

Have you verified that the SQL statement sent by PHP is what you expect? If not, I would suggest you load the query into a string, then print the string as part of your output in addition to passing it to mysql_query(). In my own coding, I have several times spotted errors this way in the prin

Re: DELETE from more than one table

Hi Michael, Thankyou for your reply, unfortunately I am unable to use InnoDB tables... From: Michael Stassen <[EMAIL PROTECTED]> To: shaun thornburgh <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: DELETE from more than one table Date: Tue, 08 Jun 2004 12

Re: DELETE from more than one table

ersion 3.23.54 and I am unable to change it! From: "Robert A. Rosenberg" <[EMAIL PROTECTED]> At 14:01 + on 06/07/2004, shaun thornburgh wrote about Re: DELETE from more than one table: Hi Paul, Thanks for the reply. I actually need to delete data from about 10 tables, is this p

RE: DELETE from more than one table

Hi Robert, Thanks for the reply but unfortunately I am still using version 3.23.54 and I am unable to change it! From: "Robert A. Rosenberg" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: RE: DELETE from more than one table Date: Mon, 7 Jun 2004 18:04:38 -0400 At 14:01 +00

Re: delete record that passed > 1 day

starofframe wrote: Thanks a lot Mike... what's a clear explanation... You're welcome. Actually I have used WHERE crea_dt + interval 1 day < curdate() before I read ur reply ...but now I guess I better change it to WHERE crea_dt>curdate-interval 1 day That's probably just a typo, but just in case, y

Re: delete record that passed > 1 day

Thanks a lot Mike... what's a clear explanation... Actually I have used WHERE crea_dt + interval 1 day < curdate() before I read ur reply ...but now I guess I better change it to WHERE crea_dt>curdate-interval 1 day again thanks a lot... and thanks to Jeff too. Sukanto -- MySQL General Mailing L

Re: delete record that passed > 1 day

Jeff Smelser wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 10:03 pm, starofframe wrote: but delete from table where date_sub(crea_dt, interval 1 day) Here: http://dev.mysql.com/doc/mysql/de/Date_and_time_functions.html I gave you the function.. You can figure out how m

Re: delete record that passed > 1 day

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 10:03 pm, starofframe wrote: > but > delete from table where date_sub(crea_dt, interval 1 day) should delete all data right??? > All I want is compared to date_now > eg : now is 2004-6-8 and all I want to delete is data t

Re: delete record that passed > 1 day

Yes, I did found out just now... but delete from table where date_sub(crea_dt, interval 1 day) To: <[EMAIL PROTECTED]> Sent: Tuesday, June 08, 2004 9:41 AM Subject: Re: delete record that passed > 1 day -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 09:22 pm, Jef

Re: delete record that passed > 1 day

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 09:22 pm, Jeff Smelser wrote: > On Monday 07 June 2004 09:25 pm, starofframe wrote: > > Hi Jeff, > > you mean I can use sql like this > > DELETE FROM table where date_sub(crea_dt, interval 1 day) > > Add: > crea_dt Could be

  1   2   3   >