Hi I am trying to show tables with names not like a pattern by mysql is throws an error: SHOW TABLES NOT LIKE "tree%"; returns: #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 'NOT LIKE "tree%"' at line 1 What is the right syntax? Thank...
I have a mysql table containing 400,000 rows whenever I run a PHP script to update one row, it takes about 3-4 seconds to do it. How can I optimize the update query? UPDATE `weakvocab` SET `times` = times+1, `wrong` = wrong+1, `mtime` = 1284369979 WHERE `owner` = 'owner_name' AND `vocID` = 'ID_number' AND `type` = 'type_name'; This ...
Working on an in-house ticketing system in php/mysql. This short term ticketing system has been in place for a year or so now. We are reaching 10,000 tickets and I would like to make the queries more efficient, since 9,500 or so of the tickets do not need to be looked at each day. Right now the queries are getting rows by tech id, the...
Hello, I was just wondering wether i can do this: SELECT * FROM calendar ORDER BY ((month * 31) + day) ...
Hey I am sorry if this has been already posted or its on internet. I came here after long search Suppose this is the table: +----+-------+----------+---------------------+ | id | name | group_id | created_time | +----+-------+----------+---------------------+ | 1 | foo | 1 | 2010-09-22 00:00:00 | | 2 | rafi | 2 ...
how do I do multiple updates within same query. Lets say I have this query UPDATE table1 SET Order = 1 WHERE ID = 1234 But I want to change some more orders where IDs are 2345 and 2837 and 8399 how would I do that within same mysql query. Please notice that Order may be different than 1 for those. as order field is unique. ...
I have a query like the following and was wondering what kind of SQL is produced by batching a PreparedStatement. INSERT INTO table1 (id, version, data) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE table1.data = IF(table1.version > table2.version, table1.data, table2.data), table1.version = IF(table1.version > table2.version, t...
I don't understand MySQL very well, here are the table structures I am using. users id | first_name | last_name | username | password categories id | user_id | name | description links id | user_id | category_id | name | url | description | date_added | hit_counter I am trying to return a result set like this, to...
This is somewhat of a multipart question, but.. I am looking to query a MySQL table to get fields from a event category table. Each category has a specific calendar assigned to it, in the "calendar" field in the category table. I am planning to have a HTML list box for each of the different types of calendars (only 4, and they wont ch...
Hi All, I have a category table with: categoryID parentCategoryID categoryName and an items table with: itemID categoryID itemName I am using MySQL. i want to write a query that will return a count of Items in a category given a categoryID. The query should return total count for all items in all subcategories of the given categ...
Hey folks :) I'm having a little trouble with an sql query. Which is why I hope some of you will be able to help me. I've got an query that selects the latest entry from each relational id but i want to select the latest where the status is different. The table looks like this. +-----------+-------------+------+-----+-----------------...
I have 4 tables, Transactions, Orders - SuperType, and then Site_Orders and Product_Orders SubType [Transactions] id PK Orders_id [FK Orders.orders_id] [Orders] Orders_id PK Orders_type [Site_Orders] Orders_id [FK Orders.orders_id] == other data == [Product_Orders] Orders_id [FK Orders.orders_id] == other data == My question is, h...
how to find the maximum value in a column with integer values without using aggregate operator in mysql ...
Hello, I am using MySQL 5.0. I have table with following structure and data. CREATE TABLE `test` ( `text1` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`text1`) VALUES ('ABC'), ('PQR'), ('XYZ'), ('LMN'); I want to display following output. Index Text 1 ABC 2 L...
I have a table that stores "comments" on "parent" items in various "languages", with the "date" (dateTime) it was added. Example data: DROP TABLE IF EXISTS comments; CREATE TABLE comments(parentid INT, language char(2), body TEXT, date DATETIME); INSERT INTO comments VALUES (1, 'en', 'english comment 1-1', '2010-09-01 11:00:00'), (1, 'e...
I have this query that searches my database for accommodation that has a type that is equal to e.g. "Hotel" it then outputs the results in an list displaying the location of the hotels. Basically because there are lets say 4 hotels in Windermere, Windermere is coming up 4 times. Is there anyway I can tell it to only display one instance ...
I have a database in the following format: idA | idB | stringValue ----+-----+------------ xyz | 1 | ANDFGFRDFG xyz | 2 | DTGDFHFGH xyz | 3 | DFDFDS abc | 5 | DGDFHHGH abc | 6 | GG ... idA and idB together are unique. I have a file (list.txt) with a list of id pairs like this: xyz 2 abc 5 abc 6 ... and I would like to pr...
I have rows such as [ISSUE] This is a sample issue [WEBSITE] A bug in the website Note that this row doesn't contain anything inside square brackets [WEBSITE] Another bug in the website [TRACKER] It is not live! Some rows will have them in the [middle] but I don't want them etc.. Assume the field is called "Title" and the table is ...
I have around 45lacs records in a table. when i performe any operation on this data its taking lot of time... Infact when i am performing alter (thats needed for my data manipulation) It took around 3 hrs. Can anyone suggest solution to this problem? ...
Table 1 : QUERY: Create table client ( applicationNo int primary key, name varchar(20) ); Insert statement: Insert into client values (1,'XYZ'),(1,'ABC'),(1,'DEF'); applicationNo | name 1 | XYZ 2 | ABC 3 | DEF Table 2: Query : Create table client ( applicationNo int, ...