Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet
There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined
What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.
View Complete Forum Thread with Replies
Related Forum Messages:
Order Records Based On Two Columns
I'm working on PM feature for my site. So far I have this table: +-----------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | subject | char(100) | YES | | NULL | | | body | blob | YES | | NULL | | | r_id | int(11) | YES | | NULL | | | s_id | int(11) | YES | | NULL | | | date_sent | int(11) | YES | | NULL | | | date_read | int(11) | YES | | 0 | | | r_date_to_trash | int(11) | YES | | NULL | | | s_date_to_trash | int(11) | YES | | NULL | | | r_status | int(11) | YES | | NULL | | | s_status | int(11) | YES | | NULL | | +-----------------+-----------+------+-----+---------+----------------+ r_id - "Receiver's ID" s_id - "Sender's ID" r_date_to_trash - "Unix timestamp when receiver deletes the message" s_date_to_trash - "Unix timestamp when sender deletes the messages" r_status - "receivers msg status, if r_status == 1 then message is in the trash s_status - "senders msg status, if s_status == 1 then message is in the trash So this is the query I have right now: SELECT * FROM pm WHERE (r_id = 1 AND r_status = 1) OR (s_id = 1 AND s_status = 1) This is the result: +-----+------+------+-----------------+-----------------+----------+----------+ | ID | r_id | s_id | r_date_to_trash | s_date_to_trash | r_status | s_status | +-----+------+------+-----------------+-----------------+----------+----------+ | 104 | 1 | 2 | 1226972079 | NULL | 1 | 0 | | 110 | 1 | 2 | 1226971762 | NULL | 1 | 0 | | 111 | 1 | 2 | 1226971891 | NULL | 1 | 0 | | 112 | 1 | 2 | 1226972033 | NULL | 1 | 0 | | 113 | 1 | 2 | 1226972462 | NULL | 1 | 0 | | 120 | 2 | 1 | 0 | 1227103836 | 0 | 1 | | 122 | 2 | 1 | 0 | 1227103841 | 0 | 1 | | 127 | 2 | 1 | 1226972346 | 1226972090 | 1 | 1 | | 128 | 2 | 1 | 0 | 1226972095 | 0 | 1 | | 129 | 2 | 1 | 0 | 1226972176 | 0 | 1 | | 130 | 2 | 1 | 0 | 1226972396 | 0 | 1 | +-----+------+------+-----------------+-----------------+----------+----------+ This is the problem: I'm trying to list the messages that are in the trash in order by date they were deleted (r_date_to_trash, s_date_to_trash). I need to join these two fields somehow together and then I could just do ORDER BY whatever DESC. That way the messages in the trash will show up in the order they are deleted.
View Replies !
Select Columns Based On Subselect
Is it possible to do the following: select (select from factors where type='factor_1') from survey_results where last_name='some_name'; I get the error: "#1242 - Subquery returns more than 1 row" Can I convert the rows to a list? More info if needed: I have two tables, 'factors' and 'survey_results'. A survey_result row contains a list of factors (> 30). Factors can have different types. For a survey result I'd like to select only the columns related to a certain type of factor and analyze just those factors (e.g., find the min and max scores). I don't want to hard code lists of factors of certain types to use in my outside select statement. I'd like to do it dynamically based on the factors table.
View Replies !
Concat Columns Based On Certain Criteria Using Only SQL?
I have a big table (15 million records). Each row has a parsed address (number, prefix, street, type, suffix) and I need to take these separate pieces and concatenate them. However, often times a few of those pieces will be blank, so I want to prevent any unnecessary spaces. I'm using MySQL 4.1. I found some documentation for using if statements for MySQL 5.0, tho I dont know if any of it works in 4.1. The basic psuedo code of what I want to do is this:
View Replies !
Column That Calculates Average Based On Other Columns?
is it possible to have a column called "average" that adds up and then averages out the data in other columns? I've got a database with several columns of scores. I'd like to have a column that holds the average score. Then on my website, my visitors can sort by the highest score and I'll be able to pull the info straight from the db.. furthermore, only certain columns hold scores, they're named like "scoreA", "scoreB" and so on... is this at all possible? If not, can anyone point me to some php resources that will allow me to calculate hundreds, perhaps thousands of scores (on the fly) and present them from high to low (or low to high)?
View Replies !
Adding Columns And Data
I have two tables. I wish to add a new column to the beginning of each table and to fill them with incremental values e.g. if one of the tables looks like this: 23 56 45 87 21 67 34 09 I'd like it to be changed into: 1 23 56 2 45 87 3 21 67 4 34 09 .
View Replies !
Adding Multiple Columns
Schema on a table has changed between versions of an application I use. I'm planning on migrating the existing table (member table as is, data and schema) and then wish to add the new columns after an existing column (eg password) and am wondering do I have to add each column at a time with multiple sql statements or can I add them all in one sql? Additional columns: `invisible` set('1','0') default '0', `pmfolders` text NOT NULL, `saveogpm` char(3) NOT NULL default '', `emailonpm` char(3) NOT NULL default '', `useoldpm` char(3) NOT NULL default '', `webcam` varchar(75) default NULL,
View Replies !
Automatically Adding Columns
I have a table that looks like this (commas indicate columns: player_id|player_name ------------------------- 1|Mary 2|Dan 3|Joe I would like to assign each player to a team, but each player can belong to more than one team, so for example: player_id|player_name|team_1|team_2|team_3 ---------------------------------------------------------- 1|Mary|LA |Denver|Boston 2|Dan |LA |null|null 3|Joe |Miami|New York|null How can I automatically add columns to my table depending on how many teams each player belongs to?
View Replies !
Adding Aggregate Functions With Columns
Does anyone know how to add the values returned from aggregate functions to the values in the columns. I have to compute the average price of a product given from a list of products which have their own prices, and display the products information with its price, the average price including that product, and the average price not including that product. so far it got this :----: select (select avg(pprice) from vendor) , (select max(pprice) from vendor), (select min(pprice) from vendor), avg(pprice) , vname from vendor a group by vname; which calculates the average but then i got lost.....
View Replies !
Change Value In Field Based On Order
I have a field that is an INT and can contain a value of 0 or greater. When the value is 0 it creates a unique situation and users of the site will sometimes change this to 0 on purpose because this value can have your item show up in the search results on the first page. Why, if a person orders the information ASC, it will show up first. I have to allow this field to be 0, long story, but I want to "punish" them if this is so. Based on the type of search (ASC or DESC) as the user can pick. I want those items to always show up last. Currently I am passing a variable that gets set via a form w/ that information, but I don't know how to check in the query how the variable is set. I was thinking CASE but I thought that only looks at a field in the db, not some variable that is being passed. Either way, I hope this is making sense. Here is the query (pretty simple). Code: ......
View Replies !
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Replies !
Adding To A Field
I need a MySQL script that will add a number to a field. Every time you click a button or something, in database "Database1" and table "Player1", the field "Test1" will then add a "5, " to it. So the first time it is "5," then "5,5," then "5,5,5," . I can't seem to get it to work.
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
Adding Up DATETIME Field
i'm struggling with updating session expire times in my login table. Can you tell me, how to correctly add an amount of seconds (after that the session expires) to a datetime field? when i do the insert for a new login, or an update, the expire field will alway end up containing zeros Code:
View Replies !
Adding Html To A Field
I am trying to add an html document to a mysql table field (type "TEXT"). What I want to do with it is create an RSS feed that gets data from the table. I cannot add the html file to the table through phpMyAdmin because it actually replaces EVERYTHING... like "<" turns into "<" and all quotes are turned into """. When I try to output the XML for the RSS feed there's no easy way to change stuff back. I cannot add it directly to the table either because nothing is escaped.
View Replies !
Adding FULLTEXT To Field
My table contains a field with free text description. I would like to add fulltext capabilities to it, but not sure how. I think the synthax is: ALTER TABLE myTable ADD FULLTEXT(myField) After the field has been indexed, I'd like to be able to search through it, and find portions containing a whole or partial phrase in any location - I thought about using wildcards, but don't really know how. Is this correct: SELECT * FROM myTable WHERE MATCH(myField) AGAINST('*some phrase*' IN BOOLEAN MODE)
View Replies !
Adding Operations To A Field
I have a table with three fields The Fields are : Wins, Losses, and Win Percentage I was wondering if I could add an operation to the 'win percentage' field that will automatically update this field when a user's wins and losses change. Is there a way to add an operation to the field or is this not possible? The equations would be Win PCT = wins / (wins + losses)
View Replies !
Updating A Database Field Based On A String Or Filename
I am using MySQL to store images that I serve up with Coppermine photo gallery. Each image is shown in low resolution format. I also store medium and high resolution versions of these files for download. There is a caption field where I put these links using BBcode. It looks something like this: Code:
View Replies !
Trigger That Fills In A Missing Foreign Key Based On Another Field
To provide an easy insert methods for our client, I'm trying to write a trigger that fills in a missing foreign key based on another field that is inserted. Basically, 'dr_market_id' is inserted and I'm trying to find 'market_id' by selecting the appropriate row in the markets table. area_codes_markets.market_id is foreign key to markets.id delimiter | CREATE TRIGGER area_codes_markets_ins BEFORE INSERT ON area_codes_markets FOR EACH ROW BEGIN DECLARE tmp_market_id INT UNSIGNED; DECLARE tmp_dr_market_id INT UNSIGNED; IF NEW.market_id is null THEN SELECT id INTO tmp_market_id FROM markets WHERE dr_market_id = NEW.dr_market_id ; SET NEW.market_id = tmp_market_id; END IF; END;| delimiter ; The trigger gives no warnings. However when I try to insert a row into area_codes_markets, I get a foreign key constraints error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`area_codes_markets`, CONSTRAINT `area_codes_markets_ibfk_1` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)) The table 'markets' is populated with the correct ids. Does anyone have an idea what I'm doing wrong? If not, is there a way to print out the values of tmp_market_id and NEW.market_id in the trigger that are echo-ed to the console?
View Replies !
Adding Text To End Of Field Using A Statement
I need to update a particular field in each of a few thousand rows by adding the same text to the end of the existing (text) field contents. Is there any way to automate this process by using a MySQL statement? The contents of the existing fields are all unique so I can't do a simple replace while tagging on the new text.
View Replies !
Adding New Field To Table That Already Exists??
here is the syntax i used to create a table with certain fields: CREATE TABLE article ( id int(10) unsigned DEFAULT '0' NOT NULL auto_increment, section_id int(10) unsigned DEFAULT '0' NOT NULL, category_id int(10) unsigned DEFAULT '0' NOT NULL, approved enum('N','Y') DEFAULT 'N' NOT NULL, title varchar(100) NOT NULL, author varchar(50) NOT NULL, email varchar(50) NOT NULL, date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, PRIMARY KEY (id), KEY id (id, section_id, category_id) ); now since i have done that i want to add a few new fields: INSERT INTO TABLE article( score ??? unsigned DEFAULT '0' NOT NULL, genre varchar(50) NOT NULL, fuser varchar(50) NOT NULL, ); firsty, is the above syntax correct (i just want to add new fields, not delete any existing ones or their content)? secondly for the field 'score' that will be a 2 digit number but not an integer (will have decimal place) - what do i put instead of ??? there?
View Replies !
Adding A Date Field, Need To Populate The Fields
I have a table and until now there is was date entry for the fields. I have added a timestamp (column "time") and from now on, every time I insert a new entry, the date/time will be recorded. The thing is, I want a date/time to exist for the old fields. I have about 250 fields and I want each field to be assigned a timestamp based on the fields alphabetical order (ordered by "title"). So, field with title "aaaaa title" will have a timestamp of today. A field with a title "zzzz title" will have a timestamp of 250 days ago. It doesn't have to be exactly like this. My main goal is to assign dates to my old fields. Anyone know how to do this?
View Replies !
Adding Numeric Mysql Field Values
I am having a mysql field that holds a numeric value in each of their rows, like 2, 5, 0, 9, etc. I need to add them and get their result (2+5+0+9+ ...) Wondering if there's a short cut way to do that using a mysql command ... or, i'll have to fetch the values in an array and add them using php.
View Replies !
Adding To An Existing MySQL Field/record
I want to be able to add to an existing record. I have a table call pages, and within this i have a column called audit_history. How can i run a query so that it adds content (stored in $newHistory) to the existing audit_history field. I don't want to have to retrieve the audit history first and then add it on using PHP as this seems a waste. Once the query has been run the audit_history should contain the existing data and the $newHistory data.
View Replies !
Adding Comma Delimited List Into Mysql Db Field
I've got some checkboxes in my form. They submit as a comma delimited list. I want to be able to put this 'list' into one field that I will parse when requested. I've looked on the mysql site for info but can only find explanations that talk as though if I enclose the list with double quotes instead of single ones it will work... it however is not working.
View Replies !
Adding A Random Number To A Table Field During Import?
By habit, my ID fields are usually md5() generated - 32 character varchar field. When I import data using LOAD DATA INFILE, is there a way to generate that number for the have a table field since it is not in the infile? If not, is there another mechanism for importing that would allow for that? If not, are there operational security issues you guys are concerned with using auto-increment like being bale to guess another record number?
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
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.
View Replies !
Load Data Infile And Decomposing The First Field To More Columns
I would like to load a big file into the mysql db, therefore I want to use the "load data infile" command. the first field in the file should be decomposed to the first three columns. The first field looks something like this: 20080603_00:00:02:571. These time information should be decomposed to three columns in the database. Can I use the SET part of the "load data infile" to define some string operations or regular expressions?
View Replies !
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table: Column1 | Column2 | Column3 =========================== NULL |"Value1" | NULL --------------------------- "Value2"| NULL | NULL --------------------------- NULL | NULL |"Value3" In each row only one column contains an actual value (a string in our example), the other columns are set to NULL. I would like to merge those columns into one single column, discarding the NULL entries. The desired result would look like this: Merged_Column ============= "Value1" ------------- "Value2" ------------- "Value3" A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't work unfortunately. Is there an easy way to accomplish this on the SQL Server? The next problem I am going to face is that every column is in fact made of a different data type (ie. Date, Int). Is there a way to modify the merge select (if that's possible) to have it cast everything into a single specific type? Ie. make everything in the merged column a string.
View Replies !
How Do I SELECT FROM Columns Where The COLUMNS Has A PATTERN?
I've been looking far and wide on this one. I'm looking for a way to select from columns where the columns have a certain pattern! Example: Instead of doing: SELECT q1, q2, q3, q4, q5, q6, q7, q8, q9 FROM exam I would like to do: SELECT q* FROM exam or SELECT q% FROM exam Whatever will match everything that has the letter q in it. Anyone have an idea? I've been banging my head on this one.
View Replies !
Select Based On FK
I have two questions regarding the MySQL v4.1.16 scenario below: 1. What should the FK on update and on delete parameters be (i.e., default, cascade, restrict, no action, etc.)? 2. How can I insert the correct id’s as implied below? I have tried just about everything (including “Insert …select”, “LAST_UPDATE_ID();”, etc.) and whenever I think I am starting to get close I either get more errors or ‘0 records added’ with no error message; I would greatlyappreciate someone who could instruct me on how to accomplish this task.
View Replies !
Using A Web Based Database
My web site host allows me to create MySQL databases on its database server. I've created a database as a trial. Now I would want users of my website to supply data to the database and for me to be able to extract it for analysis.
View Replies !
Web-based Chat
I'm debating between 2 different table setups for a web-based chat system (gchat clone). The first is just one table labeled 'chat' which has the columns 'user_1' 'user_2' 'time' 'message'. The second would be dynamically creating/deleting tables with the user names as table names like 'user_1/user_2'. I don't know a whole lot about DB table organization but I was thinking the actual 'select' statement would be faster with seperate tables. Any ideas?
View Replies !
Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns? If I have to make so many different kind full text index, that will slow my database performance or not? 2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
|
TRACKER
