Ignoring Rows According To Values In Another Table
I have the following query:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db. Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4. What I would like to do is take the entire row and duplicate it by the number of the quantity field. This would result in the same product 4 times instead of one product with qty of 4. Reason, I am going to serialise all the products so that they each have unique barcode from the id field. I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??
Values From Different Rows
I have Orders and Payments. Sometimes the Orders do not have Payments. So, I decided to use LEFT JOIN. Furthermore, some Orders have 1 related Payment, and some Orders have 2 or even more related Payments. Now, I want to combine the sum of all Payments and the last Payment in one row. select Orders.SeqNr, sum(B1.Amount) Total_paid, max(B2.SeqNr) Last_record, B2.Amount Last_amount from Orders left join Payments B1 on B1.FK_Orders = Orders.SeqNr left join Payments B2 on B2.FK_Orders = Orders.SeqNr group by Orders.SeqNr If there is only 1 payment per order, then I will see perfect results. However, if there is more than 1 payment per order, then the results are wrong... The column Last_amount does not contain the amount which belongs to the column Last_record.
Replace Values In One Column All Rows
I need to replace all values in one column with the values from another column - example: I want to replace all the values in the Username Column with all the values in the EmailAddress column - all rows to be effected.
Entering Dynamic Values Into Rows?
Is it possible when entering values into a table, to enter a string value from one column into another column in the same row in the same insert command? Is it possible in the insert command, to call some predefined function to get the appropriate value for the 3rd column? In calling this function, i would use the value from the 2nd column as the argument. this (theoretically), could look somthing like this: INSERT into IMAGES values (NULL, 'images/image_01.jpg', 'func(col2.value)'); the function would be somthing like: (excuse syntax, i haven't learned MySQL functions yet, but just to give an idea) function func(string s) { select str = s select str = SELECT REPLACE(str, '.jpg', ''); select str = SELECT SUBSTRING(str, 8); return str; } The resulting inputted values would then be: (NULL, 'images/image_01.jpg', 'image_01'); If this were possible, it would save me a LOT of uneccesary typing, as for each insert command, i'd only have to change the path name for col 2 and not type the name for col 3.
Selecting Rows Whose Values Begin With A Certain Character
Let's say my table just contains just one column of words: apple bear banana water salt sugar cat zebra How would I write a select statement that selects all rows where the word begins with the letter b, s or z? SELECT word FROM mytable WHERE word (code goes here) Would I need a case statement for this?
Altering Values In Rows Into Sequential Numbers
I have a database that I need to import into another application that has a different database structure/format. I changed as much as I can to make it compatible for importing except for one thing. The old database uses the category and parent category for referencing whereas the new one uses category_id and parent_id Quick example: (the tables have more fields but this is all I'm trying to figure out right now) Code:
SQL*plus Inserting New Row With Values = To Existing Rows Data
I am trying to insert a new employee into an empolyee table and get some of the values (namely phone no.) to be the same as what another employee currently has. This new employee is replacing the old employee, but i have to keep the old employee alive in the db as other data is reliant on its existance. I was wondering how do i insert the new employee asking the, VALUE ('etc', 'etc',) line, to assign a value to certain fields equal to the value contained in other employee rows.
Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value. example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates). how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?
Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A.. Example: B.part = "abcdefg" matches A.part ="cde" I cannot put literal in query.. it must be from column data.. It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.
Ignoring Punctuation
I can't believe I couldn't find this answer myself, but all I'm turning up are answers for C programs.How can I tell MYSQL to ignore punctuation when doing its ORDER BY thang alphabetically? All my results in quotations are listed first. I want to preserve the quotations for user-readability, but I sure don't want to sort by them.
Ignoring Spaces
When a search is gone on Google it is capable of taking something like "R.J. Gators" and returning results with "RJ Gator's", "R J Gators", "RJGATORS", etc. I have run across a scenario in which I need to do similar. I am trying to avoid the need to create a table entry without every single variant possibility and without creating a bunch of LIKE statements just to search for a single record that could vary every so slightly in the way it is spaced or punctuated. For example, I have one table with "R.J. Gator's" as a value and some additional information but unfortunately that is not always how the phrase is setup when the record lookup occurs, though the letters themselves will always be consistent.
Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure: id, x, y, owner Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields. now the problem: New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25) What I need is a query that gets those fields that have vacant fields around them. So far, all my tries to solve this problem with Joins/Suvqueries failed.
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
Ignoring Non-alphanumeric Characters...
Is there any way to do string comparisons and ignore all non-alphanumeric characters? For instance, check "foobar" and have it match an existing record of "f$#!oo B(a)*R".
Sorting A-Z Only While Ignoring " . - ?
I would like to generate a dictionary with terms from a-z. However, some terms begin with " or ., like "slang" or .jpg Now .jpg is sorted under "." not "j"... is there a simple way to fix this?
Ignoring NULLs In Indexes
Access has a nice feature that allows creation of indexes WITH IGNORE NULL which means that records with null values are ignored in the index, so the index will be shorter and faster to search. Is there any equivalent for this in MySQL?
Ignoring Blank Fields
Im having a problem where im allowing the user to identify a row by 1 of 2 unique fields. The problem i have is that my current query states to either take a OR b... so my query looks like this. a = $a OR b = $b Now these unique fields can also contain a result of null. Which means that im returned with more than 1 result when only 1 of the unique fields is entered. Is there a way of escaping results that contain null in either of the fields?
Ignoring Non-alphanumeric Characters
Is there any way to do string comparisons. and ignore all non-alphanumeric characters? For instance, check "foobar" and have it match an existing record of "f$#!oo B(a)*R".
Ignoring The HTML Tags
I have a field where i may have the HTML tags with the actual data. I want to search through this field, ignoring the HTML tags and only the displayable data. Eg. if i have field value <font style="padding-top:2px;">style</font> Then this record should be returned only if i search for style. For font, top, pad it should'nt be returned. I think regular expressions will be useful. I am using MySql 5. Would be helpful if you provide the whole syntax as i am new to MySql .
Select Date Ignoring Time
Im using a datetime fields eg. "2006-08-07 16:01:09" and I want to select dates... eg. "select * from mytable where mydate="2006-08-07" ignoring the time.
I Get Ignoring Query To Other Database Message
Does anyone know why MySQL Monitor generates this message: Console: Ignoring Query to other Database I get this when I try to do anything but the "use databasename" command. example: Console: use users>create table person1 .....
Count() Ignoring Parts Of Where Clause
I have a select count() that seams be ignoring one clause. Data: Table: ID CA-Char CB-Int 1 dsfsd 6 2 dsfsd 0 3 dsfsd 1 4 sdrtt 1 SQL is: "SELECT count(id) FROM Table WHERE CA-Char = 'dsfsd' AND CB-Int > 0" Returns 3 - Should be 2 Does anyone know what could be going on? I have tried searching with no useful results, most examples imply this should work.
Stop Words In Mysql - Ignoring A/an/the
What are the mechanisms to make words like a/an/the in text and varchar character fields ignored when performing SELECT and utilizing ORDER BY in SQL queries? I've done some hunting around into documentation on character sets (but they are geared mainly at character-by-character rules) and saw some reference to string collating and stop words -- but not seen any of this all put together.
INSERT Query Ignoring Escape Characters
I have extensively searched the web, and none of the solutions I've found seem to work (mysql_real_escape_string, addslashes, str_replace). Php will display the reformatted strings properly, but when I attempt to add a reformatted string to an INSERT or UPDATE query, the escape characters seem to be ignored. On INSERT, fields that contain the " ' " single quote (like Joe's) are losing all data after the single quote. The query DOES insert the record, and the rest of the record is inserted properly, but the field containing the single quote is truncated. "Joe's Crabshack" gets stored as "Joe" ....
Ignoring Special Characters In Select Search
I have a database with song titles, and some of these contain special characters like "We're going down", where the ' makes some trouble. So what I want is for the search to ignore that character. So that using "SELECT * FROM Lyrics WHERE Song LIKE 'were going down'" will return a match on that song if Song has an entry of "we're going down". Is this possible to do in the select query? Or does anyone have an alternative solution?
Ignoring Content Inside HTML Tags
Is it possible to set MySQL to by default ignore all content contained within HTML tags? For instance, if a database full of HTML pages has the "class" attribute on a lot of the tags and someone searches for "class" expecting information on classes, wouldn't that also return all those documents just because they had a "class" attribute? It would be great if I could set MySQL to ignore all content between < and >.
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211). mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 5359211 Avg_row_length: 801 Data_length: 4294967288 Max_data_length: 4294967295 Index_length: 45783040 Data_free: 0 Auto_increment: 5406252 Create_time: 2007-04-20 18:26:38 Update_time: 2007-08-22 09:55:22 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) Here is the table structure. CREATE TABLE `messages` ( `id` int(11) NOT NULL auto_increment, `subject` varchar(255) NOT NULL default '', `message` text NOT NULL, `attachment_path` varchar(255) default NULL, `new` tinyint(4) NOT NULL default Ɔ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; On searching I found the this link. http://dev.mysql.com/doc/refman/4.1/en/full-table.html According to manual i executed the following query MySQL ALTER TABLE messages MAX_ROWS=20000000000; I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this. After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left. mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 170109 Avg_row_length: 649 Data_length: 110563036 Max_data_length: 281474976710655 Index_length: 1961984 Data_free: 0 Auto_increment: 5409214 Create_time: 2007-08-23 10:07:08 Update_time: 2007-08-23 13:41:57 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) What could be the reason of this rows deletion? I am on 32 bit system. MySQL version: 4.1.18-standard-log Operating System : CentOS 3.x Memory: 4 GB DDR
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
INSERTing Into A Table, With Values From Another Table
I'm programming a shopping cart application, where the products are categorized into different lines. I have a table that defines the different product lines, and now want to populate the product table. However, the text file with the products in it, which I will use to populate the products table, uses the line NAME for each product, when I wish to use the line ID instead. To get the line ID, I would need to get the value from the line table. My question is: is it possible to populate the products table with data from the products text file, as well as a column from the lines table? That is, the INSERT (or LOAD DATA) statement will insert column values from the text file, but the line id from the lines table instead of the line name found in the file (WHERE line name = line name in the lines table). I'm still not sure whether to use an INSERT, and write PHP code to go through the text file, or to use LOAD DATA for this task, because I don't know which statement would support what I'm looking for.
Insert Table Values From Another Table
I have two tables: Users and metadata Users: id userid username Metadata: id userid value I would like to extract a list of the userid's from the users table and enter them into the metadata table, with an additional value. Example: I want to take all of the userid's from the User table and insert them into the Metadata table, and make the value="one" for each entry into the Metadata table.
Update One Table Value With Values From Another Table
I am trying to update one table value with values from another table, and I cannot get it to work. What am I doing wrong? This is my SQL-command: UPDATE tabel1 SET tabel1.name=tabel2.name WHERE tabel1.ID=tabel2.ID
Inserting Values From 1 Table Into Another Table
I have 2 tables: A and B Table A contains fields u, x and y Table B contains fields u and z Table A has all possible variations of u. Table B has some of the varaiations of u in it. My problem is that I'm trying to insert the z-values from Table B into Table A's y field and link them up based on the u-values. I'm running into problems like truncating at row 0. I've tried the following queries: update TableA set y =(select TableB.z from TableB where TableA.u=TableB.u) update TableA,TableB set TableA.y =(select TableB.z from TableB where TableA.u=TableB.u)
Add Values To Table
I have a table tblMailingList. It has 3 columns; Name, EmailAdd, AutoID. I am using mysql> INSERT INTO pet -> VALUES ('name1','u@me.com'); but I am unsure as to what to do for the AutoID field. If I use the example given, the error message says that the number of fields is different. How do I get this info into my table?
INSERT Values From One Table To Another
In table A i have a.id and name and in table B, i have b.id, a.id(FK) and owns. Table A -------------- a.id | name | -------------- 12 | James | 13 | Paul | 14 | Tom | 15 | Andy | Table B -------------- b.id | a.id | owns | -------------------- 1 | 12 | Car | 2 | 12 | Bike | 3 | 13 | PC | Now what i want is to add two new names to table A which will be Tom and Andy and have the same data in table B for Tom and Andy with just different b.id (PK). How can i insert this new data in table B using insert?
Update A Table With Values In Another
I have the following table tableA column_a column_x column_y column_z tableB column_x column_y column_z How do I update the rows of tableA that match rows of tableB (all rows of tableB are unique) What I want to acheive is a check against tableA.column_x with tableB.column_x, if they match, then update the row in tableA.column_y with the value of tableB.column_y and tableA.column_z with tableB.column_z
Edit Values In Table Through GUI
I wonder which GUI tool I can use if I want to edit some values in a table? I can't find it in Query Browser. Do I really need to execute SQL to make a simple change to a field?
Update Table Values
I have a csv.txt file with contacts and their information in it. Then I also have a table in mysql with the same contacts and their various information. I need to merge these to so that if a field value in the database is NULL the value that is in the csv.txt file is inserted into the database field. I have been looking at the mySQL site on how to do this but have not been able to produce a working solution. Below is a more detailed example of what I am looking for. Code:
Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other. Say we have two tables (table_1, table_2) each with a column titled "appointments". I want a list of results for appointments which are in table_1, but not in table_2 I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables: SELECT table_1.appointments FROM table_1, table_2 WHERE table_1.appointments<>table_2.appointments; I've also tried 'NOT IN' for this but the result was just the same.
Editing Values In A Table
I just moved from SQL Server to MySQL and in the Enterprise Manager in SQL Server I could query the entire table and then modify the values with the table without writing an UPDATE statement. I cannot do that in one of my tables because the EDIT at the bottom is greyed out. There are other tables where I can make a modification to any value I want within the table.
Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning. current query looks like this: SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id; So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?
Inserting Values Into A Mapping Table
I am using a mapping table with two primary keys (ID1, ID2). When I try to insert data into this table I get the following error: Quote: Violation of PRIMARY KEY constraint 'PK_myTable'. Cannot insert duplicate key in object 'dbo.myTable'. The statement has been terminated. I am able however to insert values if I do it directly into my table. Here is an excerpt of my comm = New SqlCommand( _ "INSERT INTO myTable (ID1, ID2) " & _ "VALUES (@ID1, @ID2)", conn) ' Add command parameters comm.Parameters.Add("@ID1", System.Data.SqlDbType.Int) comm.Parameters("@ID1").Value = ID1List.SelectedItem.Value comm.Parameters.Add("@ID2", System.Data.SqlDbType.Int) comm.Parameters("@ID2").Value = ID2List.SelectedItem.Value
Generating A Table With Missing Values In SQL
I have the following table: +------+------+------+-----------+ |ID |GROUP |CODE |ANSWER | +------+------+------+-----------+ |1 |1 |001 |01-01-2003 | |2 |1 |002 |Yes | |3 |1 |003 |NA | |4 |1 |004 |297797 | |5 |1 |005 |0 | |6 |2 |002 |Yes | |7 |2 |004 |297852 | |8 |2 |005 |1 | +------+------+------+-----------+ This means that code 001 and 003 of group 2 are missing. Result of the query should be: +-------+-----------+-----------+-----------+-----------+--------+ |GROUP |ANSWER1 |ANSWER2 |ANSWER3 |ANSWER4 |ANSWER5 | +-------+-----------+-----------+-----------+-----------+--------+ |1 |01-01-2003 |Yes |NA |297797 |0 | |2 |NULL |Yes |NULL |297852 |1 | +-------+-----------+-----------+-----------+-----------+--------+ The missing values should be replaced by a NULL value in my resultset. Is this possible? I don't have any clue how my query should look like, can anyone help me and show me a solution?
Eliminating Double Values From A Table
I need to consolidate data from many adress databases, which i allready sucessfully put into one table1. As Newbie i am reviing the Manuals and tutorials, but in 2 days i learned a lot about the powerfull command, but not how to solve the following question: Is there a straight forward method to copy data from one table1 into the other table2, but not those rowse, which have same value in Fieldx. All my Update and INSERT SELECT experiments somehow created more then less data in the target table.
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
Insert Values Only If They Do Not Already Exist In Table
I want to insert some values from one table (A) into another table (b). However there are some values in table A which exist two times in table A. I only want to insert values one time. So If a certain field value also exist in some other row (same fieldname) then I don't want to insert it twice. It doesn't matter which of the two rows will be inserted. Can this be done with MySQL? Maybe with some Control Flow Functions?
Updating A Table To Increment Values
Question, if I want to update all values in a certain column to increment by one, what statement could I use? In quasi-MySQL, this is my pseudocode: UPDATE table SET fields=[previous_val+1] where [previous_val]>[my_val] Or do I HAVE to run a SELECT query, increment the value, and THEN Update it? I was just thinking there'd be an easier way.
|
TRACKER
