How do I get more than one row from an SQL search using MIN/MAX/AVG

I have a products table where each product has a color. I need to return the minimum product price for each color.SELECT MIN(price) FROM 'mytable' WHERE color="red"will return the minimum price for all products colored red. And I get a single row returned.What I need to do is return multiple rows with the minimum price for each distinct color.But I don't know how to feed the results of a search to"WHERE" and get multiple rows.I triedSELECT MIN(price) FROM 'mytable' WHERE color=(SELECT DISTINCT color FROM 'mytable')But I get an error. All my searches point to sub queries, b...

Camel Blueprint specify parameter for prepared sql statement

I have a poll enrich which enriches a POJO with the result of an SQL query (from a MySQL database). It currently gets the brand from the POJO and then gets the name from the order matching the brand. I had to add quotes around the ${body.getBrand}, else the query would look for a column with the brand name instead of using the value. Currently it looks like this:<pollEnrich id="_enrich1" strategyRef="merge" timeout="5000"> <simple>sql:SELECT name FROM orders WHERE brand= '${body.getBrand}'</simple></pollEnrich>I want to change it because I'll prob...

MySQL TIMEDIFF(DATE, DATETIME)

I have two columns in MySQL database.One is in DATE format like 2014-01-26, another one is in DATETIME format: 2014-01-25 17:19:07.I need to apply TIMEDIFF(2014-01-26, 2014-01-25 17:19:07) function, but it requires both variables are in DATETIME format. How can I convert 2014-01-26 to 2014-01-26 00:00:00? You can always cast a Date to a datetimeselect timediff(cast(<yourDateColumn> as Datetime), <yourDatetimeColumn>)But I'm not even really sure that you need to cast (depending on your mysql version), I may misunderstand the doc, but we can read Prior to MyS...

Create a trigger annually in mysql

I need to create a trigger annually and updates profit using mysql..The table related to these question is ,create table annual_document( documment_id int not null AUTO_INCREMENT, income decimal(4,2), expence decimal(4,2), PRIMARY KEY(documment_id));I try this one using different method.but i'm unable to create trigger which is execute annually.most of the documents describes execute event annually.not the trigger.but I need a trigger.Can someone help to me.. That may what you are looking for:CREATE EVENT myeventON SCHEDULE EVERY 1 year DO call ddwhere dd is a PROC...

MySQL Server 5.5 : unable to set password for the MySQL "root" user

when updating MySQL Server on Ubuntu, installer asks me if I want to update my root password. Whether I do change it or not, I get the following error : unable to set password for the MySQL "root" userdpkg error : Setting up mysql-server-5.5 (5.5.44-0ubuntu0.14.04.1) ... 150803 22:14:05 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead. 150803 22:14:05 [Note] /usr/sbin/mysqld (mysqld 5.5.44-0ubuntu0.14.04.1) starting as process 4824 ... start: Job failed...

Need to change my Default mysql password

I have installed MYSQL in my machine. My default there is no password. But i want to protect my database with a password. Can anyone please send me the steps to change the password.Thanks in advance. on mysql promptSET PASSWORD FOR 'root'@'localhost' = PASSWORD('cleartext password');FLUSH PRIVILEGES;or on mysql prompt use mysqlupdate user set password=PASSWORD('your_new_password') where User='root';flush privileges;or on the shell prompt-mysqladmin -u root password “newpassword”mysqladmin -u root -h host_name password “newpassword”sudo /etc/init.d/mysql restartsee more ...

Best way to handle apostrophes when searching a MYSQL database

I am currently usingMATCH column_name AGAINST ( 'search_term' IN NATURAL LANGUAGE MODE);where column_name is full text indexed. This works for the majority of cases but if the user searches for "items" I would like it to return both "short string items" AND "short string item's" from the DB (assuming both exist). Currently the way it is searching it treats the apostrophe as a letter rather than a word break such as a space. I saw some suggestions that I create another column of basically search friendly terms, in which case I could strip out the apostrophes but I was wond...

Complex query to update table field. Is this possible?

I have two database tables called Item and Inspection. An Item has many inspections. Basically, an inspection is done on an item and the inspection records it's state of bring "not broken" vs. "broken". Regular inspections are made on each item.I want to update a newly added field for each record in the Items table called "last_broken_at". It needs to be updated to the "created" datetime found in the oldest related Inspection for that Item, where the Inspection "status" field is equal to "broken".While that is definitely possible via a single MySQL update statement, here is...

MySQL full-text search in Rails?

When I added search functionality to my first Rails app, I used Sphinx, after reading that using MySQL's built-in fulltext search was a bad idea. While Sphinx works well, it's a bit complicated to set up, and I feel there's too much overload for the simple searching functionality I require in my app.Searches aren't performed very often on my site (at most one search every 3-4 seconds), so I'm not too worried about load.My question: Why exactly is using MySQL's full text search a bad idea, compared to Sphinx/Ferret/Solr/etc..? MySQL is a relational database and not a sear...

How to do 3 table JOIN in UPDATE query?

I asked a question and got this reply which helped. UPDATE TABLE_A a JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b SET a.column_c = a.column_c + 1Now I am looking to do this if there are 3 tables involvedsomething like this. UPDATE tableC c JOIN tableB b JOIN tableA amy question is basically... is this possible to do 3 table join on an UPDATE statement? and what is the correct syntax for it? Thank you. Do i do the... JOIN tableB, tableA JOIN tableB JOIN tableA the answer is yes you cantry it like thatUPDATE TABLE_A a JOIN TABLE_B b...

How to refactor/rename tables, fields and values in a large database quickly?

I have a MySQL database that has more than 100 tables with a lot of fields and setting values. We are updating our codes and database structure every few days. What is the quickest/easiest way to refactor/rename our database tables, fields, values and etc? Ruby on Rails is good at this http://edgeguides.rubyonrails.org/active_record_migrations.htmlPHP also has a variety of Active Record implementations that might help.Otherwise I'd just create SQL scripts and commit them to source control along with the code changes. [XXX]

InnoDB full-text search (no lucene)

I have a problem. I have a managed VPS server running MySQL 5.1.x. I am currently building a new database where I want to store tweets (via search, stream, timeline etc). So I want to use InnoDB database engine because of the row locking! But unfortunately, MySQL 5.1 does not support Full-text search in InnoDB tables.The problem is that I cannot update my server by myself. So I cannot install MySQL 5.6 (that should support Full-text search) and I cannot install lucene (or solr or whatever).Are there other options to achieve Full-text search in MySQL or whatever. Or maybe in...

How to save query results column into a column in table in same database

I'm having difficulty copying the results of this query (a column titled "FIPminus") into a table starting_pitcher_stats that I have in the same database.When I run the following code, the results appear as in the screenshot beneath the code below. However, when I use an UPDATE statement below the ALTER TABLE statement, as discussed in the following thread, I keep getting the following 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 'SELECT IF(s.HomeAway_CD ='Home'stackoverflow.com...

Amazon Aurora is slow compare to Amazon RDS

I have created one instance of Amazon Aurora in Sydney Region and restored my RDS snapshot on it. I am executing one simple query on one of my table which has roughly 6k records in it, returns a very slow result. I have not changed any parameter in default parameter group, which is linked to my instance. This query runs perfectly on my existing RDS instance with same parameters with 0.200sec and returns quick response. But the same query takes about 0.350sec on Aurora. My query plan (EXPLAIN) shows me no issue. It uses PRIMARY Index to get a result. So, I can't understand, ...

Change users mysql password with a view to security -- Best Practices

I am running MySQLserver version 5.0.96 on SLES 11 SP2, and I am looking for a safe way to change a user's password. Of course, it is very easy to change a user's password by:SET PASSWORD FOR 'user'@'192.168.0.%' = PASSWORD('mysecret');But this query finds its way into the query log, the binary log, and the MySQL history file. To prevent this, I used:export MYSQL_HISTFILE=/dev/nullbefore starting the client, and:SET sql_log_bin = 0;SET sql_log_off = 1;afterwards. Grep and mysqlbinlog prove that my plan comes together, but I have overlooked that MySQL has silently changed th...

Moving form Full text search in Sql Server To MySql

I already have a product in production where i used Entity Framework with SQL Server as db. I've used full text search wherein i store binary data (filestream) along with the file extension which is used by the Full Text Search Engine to index. Not to mention that was a cake walk.Now, am planning to move to mysql (cuz of obvious reasons -- cost, open-source etc). The product is in SAAS model (although this is the time i can actually take definite call as traffic isn't high) so volume will be high, so search engine should be scalable.Migration to mysql is easy (will be using...

Page 1 of 345  |  Show More Pages:  Top Prev Next Last