remove duplicate rows

Hi

I want to remove duplicate rows from my table.
Please refer example below.
Currently in my table the data are

id title text
1 xx xxtext
2 yy yytext
3 xx xxtest
4 zz zztest
5 yy yytest
6 xx xxtest

Now as you see the table except for the primary key(which is auto number) the datas are duplicate for id 1,2.
Now i want to remove the duplicate row for which the “id’ field is less than the duplicate rows.
I know because of my english the above statement is hard to understand. Please refer below the output which i want,

id title text
4 zz zztest
5 yy yytest
6 xx xxtest

As you see here i want to remove the duplicate rows which have less value of “id” filed.(As you can see record no.1,2,3 are deleted NOT THE RECORD NO 3,5,6)

Delete Similar Records

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:

delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField

This will delete all records from the table MyTable which have the same value for the field dupField, leaving that record which has the lowest value in uniqueField.

The complete tutorial can be found here: http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm

Change “T1.uniqueField > T2.uniqueField” to “and T1.uniqueField < T2.uniqueField”?

make a timtable in html from information from mysql

I have a table:
WEEKTIMETABLE
—————
username varchar(10)
day varchar(10)
Time varchar(10)
Subject varchar(100)(this is the description)
CourseCode varchar(10)
Semester int(1)
CourseYear(1)

I want to make a html timetable by selecting certain rows from the database using php commands.
Example

time Monday tuesday wednesday thursday friday saturday sunday
______________________
09.00 lecture party
10.00
11/00

etc….
how would i make a table like this and for each day-time slot take the info for example
select subject
from weeklytimetable
where day = ‘Monday’ and time = ‘09.00′ and username = userinsession

thanks a million for any help you can give!!

currentrecord = nextrecord;

means to process next record.

If you want the results for only one user, then filter it in you query. Take a look at the code. It’s not a working solution but it’ll give an idea. I didn’t take care of possible errors.

The table you’re using stores time and day as varchars … it may be a problem when ordering unless you prepend a 0 for times lower than 10.00. The days may become a problem too. Maybe you should order the results programatically.

<?php

// Here you build up your query. I made a guess using username
// from your session. Also you’ll need some logic to calculate
// previous_monday and next_sunday
$sql = “SELECT day, Time, Subject
FROM WEEKTIMETABLE
WHERE username = “.$_SESSION["username"]
AND previous_monday <= day and day <= next_sunday
ORDER by Time, day;

// You should have a valid connection to the db before using
// this command.
$res= mysql_query($sql);

// Get the rows as an associative array
$row = mysql_fetch_assoc($res);

// Now start building the actual html
// Display the header (first row) of your table with the day names.

9.00

// Now repeat that for every time and day
?>

In MySQL, how do I combine an Insert Statment with Static Values and Multiple fields from a Select Query?

I’m working with a lot of data and am trying to be efficient in entering the data. Here’s an example of what I’m doing now:

INSERT INTO Table1(Field1,Field2,Field3,Field4,Field5) Values(22,33,(Select X from Table2 WHERE ID = 99 Limit 1),(Select Y from Table 2 WHERE ID=99 Limit 1),(Select Z from Table2 WHERE ID=99 Limit 1))

So, I’m trying to reduce the SELECT FROM to just one query since the three values I’m getting are in the same record! IN other words, I would like to do something like

Select X,Y,Z from Table2 WHERE ID = 99

How would I format that in an Insert Into statement above?

Thanks!

it doesn’t matter. Try:

Select 22 as c22,
33 as c33,
‘John’ as cjohn,
col1 ,
col2
from Table2
WHERE ID = 99 Limit 1,

Open in New Window Select All

Create trigger for insert UUID()

Ive built a trigger for inserting a new user, but when I try and run the SQL Query I get:-
#1064 - 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 ” at line 4.

Any ideas What Im doing wrong?

Table is called `users` and there is a coloum called `priID` set to a VARCHAR(255) [Im going to reduce this though!]

Any help, would be appriciated.

Thank you

CREATE TRIGGER users_insert BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
SET NEW.`priID` = UUID();
END
;

copy paste this mysql promt / use Query Browser for create new Trigger:
mysql>
DELIMITER $$
CREATE TRIGGER users_insert BEFORE INSERT ON `users`
FOR EACH ROW
BEGIN
SET NEW.`priID` = UUID();
END
$$
DELIMITER ;

Mysql, How to enable row based log

Currently I have already enabled binary log as statement base logging, how do I enable binary logs as row base logging

The default binary logging format depends on the version of MySQL you are using:

* For MySQL 5.1.11 and earlier, statement-based logging is used by default.
* For MySQL 5.1.12 and later, mixed logging is used by default.

You can force the replication format by starting the MySQL server with –binlog-format=type. When set, all replication slaves connecting to the server will read the events according to this setting. The supported values for type are:

* ROW causes replication to be row-based.
* STATEMENT causes replication to be statement-based. This is the default for MySQL 5.1.11 and earlier, and MySQL 5.1.29 and later.
* MIXED causes replication to use mixed format. This is the default for MySQL 5.1.12 to 5.1.28.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable. (To change the global value, you must have need the SUPER privilege. This is also true for the SESSION value as of MySQL 5.1.29.)

To switch to statement-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements:

mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements:

mysql> SET GLOBAL binlog_format = ‘MIXED’;
mysql> SET GLOBAL binlog_format = 3;

An individual client can control the logging format for its own statements by setting the session value of binlog_format. For example:

mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;

In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterwards.

Pls go thur this once for detail

http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
Not what you’re looking for? Ask an Expert.

I checked with both ROW/STATEMENT formats… for the below insert statement…

insert into hi values(1),(10),(1000);

############## ROW ###################

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 10:50:04 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.30 created 090216 10:50:04 at startup
ROLLBACK/*!*/;
BINLOG ‘
hPeYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAACE95hJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#090216 10:50:27 server id 1 end_log_pos 174 Query thread_id=1 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1234761627/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
# at 215
#090216 10:50:27 server id 1 end_log_pos 215 Table_map: `test`.`hi` mapped to number 16
#090216 10:50:27 server id 1 end_log_pos 257 Update_rows: table id 16 flags: STMT_END_F

BINLOG ‘
m/eYSRMBAAAAKQAAANcAAAAAABAAAAAAAAAABHRlc3QAAmhpAAEBAAA=
m/eYSRgBAAAAKgAAAAEBAAAQABAAAAAAAAEAAf///gD+DP4A/gz+AP4M
‘/*!*/;
# at 257
#090216 10:50:27 server id 1 end_log_pos 326 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1234761627/*!*/;
COMMIT
/*!*/;
# at 326
#090216 10:50:42 server id 1 end_log_pos 415 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1234761642/*!*/;
truncate table `test`.`hi`
/*!*/;
# at 415
#090216 10:51:29 server id 1 end_log_pos 434 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

############## STATEMENT ###################
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 11:01:25 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.30 created 090216 11:01:25 at startup
ROLLBACK/*!*/;
BINLOG ‘
LfqYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAAAt+phJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#090216 11:01:36 server id 1 end_log_pos 205 Query thread_id=1 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1234762296/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into hi values(1),(10),(1000)
/*!*/;
# at 205
#090216 11:02:10 server id 1 end_log_pos 224 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

############## ROW ###################
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090216 11:05:21 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.30 created 090216 11:05:21 at startup
ROLLBACK/*!*/;
BINLOG ‘
GfuYSQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMwLWVudGVycHJpc2UtZ3BsLWFkdmFuY2VkLWxvZwAA
AAAAAAAAAAAAAAAAAAAZ+5hJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#090216 11:05:30 server id 1 end_log_pos 174 Query thread_id=1 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1234762530/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
# at 215
#090216 11:05:30 server id 1 end_log_pos 215 Table_map: `test`.`hi` mapped to number 15
#090216 11:05:30 server id 1 end_log_pos 250 Write_rows: table id 15 flags: STMT_END_F

BINLOG ‘
IvuYSRMBAAAAKQAAANcAAAAAAA8AAAAAAAAABHRlc3QAAmhpAAEBAAA=
IvuYSRcBAAAAIwAAAPoAAAAQAA8AAAAAAAEAAf/+Af4K/n8=
‘/*!*/;
# at 250
#090216 11:05:30 server id 1 end_log_pos 319 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1234762530/*!*/;
COMMIT
/*!*/;
# at 319
#090216 11:05:32 server id 1 end_log_pos 387 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1234762532/*!*/;
BEGIN
/*!*/;
# at 387
# at 428
#090216 11:05:32 server id 1 end_log_pos 428 Table_map: `test`.`hi` mapped to number 15
#090216 11:05:32 server id 1 end_log_pos 463 Write_rows: table id 15 flags: STMT_END_F

BINLOG ‘
JPuYSRMBAAAAKQAAAKwBAAAAAA8AAAAAAAAABHRlc3QAAmhpAAEBAAA=
JPuYSRcBAAAAIwAAAM8BAAAQAA8AAAAAAAEAAf/+Af4K/n8=
‘/*!*/;
# at 463
#090216 11:05:32 server id 1 end_log_pos 532 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1234762532/*!*/;
COMMIT
/*!*/;
# at 532
#090216 11:05:41 server id 1 end_log_pos 551 Stop
DELIMITER ;
# End of log file