Hot answers tagged

5

I am not sure what you mean by Bindings will be done in another function. You have an EXECUTE statement that is going to invoke your query right there and then, and that statement is not providing parameter values for your :name parameters. That is what the error message is about. Your EXECUTE statement must have a USING clause, as per the manual: ...


4

Assuming that you want - if the max(number) in the whole table is say, 27 - to update all the rows with NULL, to 28. You need a subquery or a derived table to first find this max and then join back to the table: UPDATE tableX AS t CROSS JOIN ( SELECT MAX(number) AS max_number FROM tableX ) AS m SET t.number = m.max_number + 1 WHERE t....


3

SELECT a AS result FROM tableX UNION DISTINCT SELECT b FROM tableX ;


2

MySql has no XML data type so it's hardly can help ( more help then app layer code) to generate HTML markup. It can help to create ordered stream of cells. Kind of select r.row_number, h.column_number, c.content from table_cells c join table_rows r on c.row_id = r.row_d join table_cols h on c.column_id = h.column_id order by r.row_number, h.column_number ...


2

Slight variation of @Ankumars answer. Since 'close' is less than 'open': SELECT A1.*, A2.status as current_status FROM AssignedTo A1 JOIN ( SELECT taskID, min(status) as status FROM AssignedTo GROUP BY taskId ) A2 ON A1.taskId = A2.taskId;


2

SELECT A1.*, CASE WHEN A2.taskId IS NULL THEN 'Task Completed' ELSE 'open' END AS CurrentStatus FROM AssignedTo A1 LEFT JOIN ( SELECT taskId FROM AssignedTo WHERE status IN ('open', 'close') GROUP BY taskId HAVING COUNT(DISTINCT status) = 1 ) A2 ON A2.taskId = A1.taskId ORDER BY A1.taskId; Fiddler: http://...


2

The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size . Take the full backup of 20 tables that are InnoDB. Drop those 20 tables that are InnoDB Stop mysql Delete the ibdata files Start the mysql services with ...


2

You are trying to insert 4 values in 5 columns: :Id => Id :uId => uId :name => name :address => address ??? => phone You can either add or remove Phone Add Phone: INSERT INTO tableX(Id, uId, name, address, phone) VALUES (:Id, :uId, :name, :address, :phone) If Phone allows NULL and you don't know its value (...


2

The values you're trying to insert are above the max allowed for a signed INT column in MySQL: 3088727691 > 2147483647 You can either switch it to an unsigned INT to make the max value 4,294,967,295 or a signed BIGINT to give you the room to hit up to 9,223,372,036,854,775,807.


2

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is no InnoDB Run the following SELECT table_schema,table_name, (data_length+index_length)/POWER(1024,3) table_sizegb FROM information_schema.tables ...


2

If you're running on Linux/Unix, you can do it with a bit of shell: for DB in `mysql -u root -r --silent -p -e 'show databases;' | egrep -v "_schema$|^mysql$|^sys$"` do mysql -uroot -p $DB < test.sql done Will obviously be easier if you temporarily set it up to be passwordless.


2

Since the FROM, WHERE and GROUP BY clause are identical, combine them as follows: SELECT prtner_name, COUNT(*) shipment_count, SUM(inscan) inscan_count, SUM(is_deliver) is_deliver_count, SUM(is_rto) is_rto_count FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name;


1

I finally figured it out. The main reason for the crashes and the mysql_upgrade error was related to missing and/or corrupted primary keys of the mysql table. There were also a couple missing records in the mysql.db table, which likely couldn't update previously due to the GRANT crashes and would have been related to Plesk "Database Servers" ...


1

You need 2 anti-joins. Written with either two LEFT JOIN / IS NULL: SELECT user.id FROM users AS user LEFT JOIN user_follows AS follower ON follower.users_id = user.id AND follower.shown_users_id = 2 LEFT JOIN user_follows AS followed ON followed.shown_users_id = user.id AND followed....


1

MyISAM tables have many problems and limitations, but one very convenient aspect to them in cases like this, is that the underlying .frm (table metadata) and MYI/MYD index and data files can be easily copied around since there are no transaction logs to worry about. If you don't care about the InnoDB tables at all, you could avoid the need to shrink the ...


1

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query. Doing a simple row-based group of your sample dataset: MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2; +-------...


1

Would it be better to use a NOT IN or NOT EXISTS query? Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter. This should work for what you need to do: SELECT [Call_Num], [Call_Cust_Num], [Call_Cust Name], [Call_Status]...


1

Yes, you are right. Memory is used by dormant databases I mentioned this in the following posts over the years Nov 13, 2015 : #1041 - Out of memory issue in mysql Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory? Apr 21, 2014 : Adding new tables -- memory usage increases Lots of tables ? Lots of columns ? Of course, that is less memory for ...


1

Try this SELECT a.descendant_id , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length FROM webineh_prefix_nodes_paths AS a JOIN ( SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path FROM ( SELECT a.* ...



Only top voted, non community-wiki answers of a minimum length are eligible