How To Insert New Records Based On Previous Record Values?
I have
TABLE:
year
value
It has for example,
year, code, vlaue
2007, 33, 4883
2006, 33, 4883
2007, 32, 4883
2006, 32, 4883
2008, 31, 4883
2007, 31, 4883
2006, 31, 4883
I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%.
My several attempts have led to this but which does not work.
UPDATE TABLE a
SET a.value = b.value * 1.05
WHERE b.year = 2007
AND code NOT IN
(SELECT code
FROM TABLE b
WHERE year =2008
AND value !=0)
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
Getting Next, Previous Record After INSERT
When I insert a record in a given index order I want to be able to traverse to the previous or next record of that index order, before or after the one I inserted. So for example if I insert a record with an address of "20 Heather Court" using an address index order, I want to be able to push a Next button (on a Web HTML form) to go to the record corresponding to "21 Heather Court" or, if I press the Previous button I I want to be able to go the record corresponding to "19 Heather Court". Assuming of course that both the Next and Previous records exist before I insert "20 Heather Court". If I change the index order to customer first name and insert a new record for a customer named "Harry" I then want to be able to push the Next button and see the record for a customer named "Jan" or if I push the Previous button I likewise want to pull up the record for a customer named "Garry". Assuming that the records for Jan and Garry already existed before I inserted the one for Harry and that they became the Next and Previous records with respect to Harry's after the insertion. Using an auto increment field does no good because every time I change the index order any kind of sequential relationship between the values in the auto increment field gets totally messed up. I have also tried statements like " SELECT * FROM Customers WHERE Customer_ID <349 ORDER BY `Customer_ID` ASC LIMIT 0 , 1" without success if there are 10 records before the one with a Customer_ID of 349. A LIMIT of 0,1 will start at record 0 for only 1 record. I want to pull up the record immediately preceding the one with a Customer ID of 349. In short is there some way to tell which record is Next or Previous to one that I insert under a given index order?
View Replies !
Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to get a resultset of previous and current and next record when the resultset is sorted by id and id=10. The table data is shown in the following: id Name Price 1 : : 5 : :<--------------previous record 10 : :<--------------id=10 12 : :<--------------next record 14 : : How do I write the sql statement if I do not know 5 and 12 are the previous and next id respectively?
View Replies !
Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example: select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate; eventDate is normal column from table previousEventDate is an "alias" for the previous date ... how can I write it in SQL? Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.
View Replies !
Get Previous And Next ID Of Current Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
View Replies !
Phase Previous And Next Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
View Replies !
To Find The Previous Record
I am preparing a ledger using PHP and mysql....I just want to bring the previous closing stock value as the opening stock value of the current transaction.For that i need to find the closing stock value of previous transaction....Is it possible to write a query to find the previous record?
View Replies !
Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project. Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column. How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records: id - list_order 102 - 100 89 - 200 7 - 250 30 - 300 97 - 400 20 - 500 62 - 600 88 - 650 103 - 700 85 - 800 112 - 900 95 - 1000 So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.
View Replies !
Comparing Difference Between Current Record And Previous, Non-sequential/variable Data
I have a locally-developed asset tracking system in which inventory operators upload batches of time stamped CSV data into a “move_log†via a handheld barcode scanner after they take an inventory of what assets are in a particular location. I would like to be able to identify each time an item actually moved from one location to the next. Sounds simple, however: 1. An item may be inventoried multiple times without actually moving (think large items that don’t move very often, if ever). This means an entry in the “move_log†table for each inventory sweep, even if no change in location. 2. An item may move from one location to another, then back to the original location, perhaps several times. Think items such as laptops kept in a central depot and checked out for use. This means that a simple “SELECT DISTINCT†will not show the actual number of moves, just the number of different places an asset has been. Unless I am missing something, the best way I can think to describe a “move†is to look at the entries for each asset, and then compare with the previous entry for that asset. Any difference between the current entry and the previous would count as one “move.†Of course, there is no set number of entries for a particular asset. Some assets may have been inventoried once or twice, others dozens of times. I would like to do this entirely within MySQL if possible. The table in question: ...
View Replies !
ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?
View Replies !
Carry Forward Column Values From Previous Rows
valuea, valueb, valuec, valued, e, f, g, .... null, null, null, valued, e, f, g .... null, null, null, valued, e, f, g .... valuea, valueb, valuec, valued, e, f, g, .... null, null, null, valued, e, f, g, .... etc. The null values in the first three fields should be replaced with the previous a, b, and c values. I'm loading the table using load data infile but I can't quite get my head around carrying these values forward. Can I do this with just an SQL statement, perhaps using ifnull(), or do I have to go to perl, or php, or similar?
View Replies !
Retrive Record Based On Results
Is there anyway to create a loop in mysql so that I can use a variable to return a record, and then get variable from that record to return another record and so on until the variable from the last result set it 0
View Replies !
Deleting From A Table Based On Record Id
I need to execute the following query: delete from history where id=(select min(id) from history where user = username); this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause" How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.
View Replies !
SQL To Get Lastest Record Based On Date Of Entry
Here's my table: Activity - Id (PK) - ProfileId (FK) - Date Each record represents a profile's activity in the CMS we're building (e.g. user updated profile, user uploaded photo, etc.) I'm only interested Id, ProfileId, and Date of the latest activity. How do I get that?
View Replies !
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
LIMIT Based On Unuiqe Values
i have a query where i search the table on the table using an alias (a in this case) and then find all rows for that user using the alias b, but now i need to limit the results to say 5 unique rows from table a but allowing any amount from table b, also table a may return multiple uids for one user Code: SELECT b.uid, b.field, b.value FROM user_s as a, user_s as b WHERE b.uid=a.uid <where clause for table a here> GROUP BY b.field, b.uid ORDER BY uid
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Enforce A Certain Constraint Based On Several Records
I'm trying to create a table "Works_In" as follows: CREATE TABLE Works ( EID INTEGER NOT NULL, DID INTEGER NOT NULL, PctTime INTEGER NOT NULL, PRIMARY KEY (EID, DID, PctTime), FOREIGN KEY (EID) REFERENCES Employee (ID), FOREIGN KEY (DID) REFERENCES Department (DID) ); The PctTime field shows the percentage of time that a given employee works in a given department (one employee can work in several departments). My question is: how can I enforce that the PctTime value <= 100 ?? For example if the employee "John" works in Department "1" 20% of the time, and in Department "2" 80% of the time...how can I make sure that the total percentage is equal to 100%...
View Replies !
Getting Records Based On Chars In A String
I have a table with 2 fields. Field 1 contains Names. Field 2 contains a single character A,B,C,D or E. I have a string "BCE" for which I want all the names that contain one of those characters (B,C, or E). For Example: Field1 ====== Jo Ron Mary Sue Mark Frank Field 2 ======= E D A C D B Desired Result after Query: =========================== Frank Sue Jo SELECT * from TestTable Where Field2 = SomeFunction("BCE") How can I do this using a SELECT statement?
View Replies !
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 !
Getting A Count Of Records Based On Inner Relationships
I am making a PHP-MySQL forum. I have a table "forum" that holds all the posts. Fields in forum: ID, user, title, body, threadID, datePosted. All top level posts (ie, original posts for a thread) have a threadID=0. All other posts are comments, hence their threadID is set equal to the 'ID' of the original thread post. I can get a list of the last 10 top level thread posts: select * from forum where threadID=0 order by ID desc limit 0,10; Also, I can get the # of comments for an individual thread (for instance, thread # 37): select count(*) as Expr1 from forum where threadID=37; How in the heck do I form those two statements into one statement? In my main forum view where I list the last 10 threads, I want returned in each result set the ID, title, user, and the # of comments posted in that thread.
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 !
How To Group Similar Records Based On Their Content?
I have a table that holds logging information. It contains 3 columns: Message_ID: The ID of the message (primary key) Message_Datetime: The datetime when the message was posted Message: The actual textual message (varchar(255)) I would like to know what is the distribution of the various messages. What I want to perform is a query that will return the number of messages of each type, where the type is defined by the first 10 characters of the message. What I mean is that all the messages that begin with the same first 10 characters should be grouped together. I want to have a query that will display for each group, the number of entries. This way I will be able to sort the results and will know what is the most common message group.
View Replies !
How To Insert A Calculated Value Based On Diff Column
let's say I have 3 columns create table test1 ( id int, dateCreated timestamp default current_timestamp, localDateCreated datetime); the localDateCreated is always 8 hours ahead dateCreated. so when i insert id number, lacalDateCreated will be calculated based on dateCreated. How to do that? I thought trigger might help, but it gave error mysql> delimiter | mysql> create trigger trgtest2ai after insert on test2 -> for each row begin -> update test2 set local=new.ts where id=last_insert_id(); -> end; -> | Query OK, 0 rows affected (0.03 sec) mysql> insert into test2 set id=10; -> | ERROR 1442 (HY000): Can't update table 'test2' in stored function/trigger because it is already used by statement which invoked this stored fun ction/trigger. mysql> delimiter ;
View Replies !
Partially Updating Records Based On Pattern Match
I would like to know if it is possible to a (string) replace on existing records based on a given pattern. Let's say I have a table containing the following records (strings): Windows/98 Windows/98/Registry Windows/2000 Windows/2000/Registry Is there an SQL method for scanning all records in the table for the pattern "Windows/98" and then, when a match is made, replace only the "Windows/98" part of the string with "Windows/2003"? I am having a hard time figuring this out.
View Replies !
Is It Possible To Sum The Values Of Multiple Fields On A Single SQL Record?
I have fields for Goals and Assists. I want to take those 2 fields and sum them to make a 3rd field PTS, then send them to variable in my PHP code to be printed to the screen. My statement is this: SELECT player_stats.G, player_stats.A, SUM (player_stats.G + player_stats.A) PTS FROM player_stats WHERE player_stats.PID =10 AND player_stats.season =1 The statement breaks with the bolded part.
View Replies !
1 Record To Multiple Records
I'm looking for an efficient way of doing the following... table1 - groupName, firstGuy, secondGuy, thirdGuy,... sample - 'Red', 2, 6, 3,..... table2 - guyId, firstName, lastName sample - 2, 'Jim', 'Smith' ; 3, 'Bill', 'Adams' ; 6, 'Tim', 'Jones' If I queried groupName = 'Red', how do I get the result to be the names from table2. Subqueries?result to contain: red, Jim Smith, Bill Adams, Tim Jones I hope this is clear.
View Replies !
Insert Using Some Data From Another Record
i have a table with four fields: (one, two, three, four) the first three fields make up a composite key. i want to insert multiple records that just changes the value for field "one". example: current record: (11, 22, 33, 44) i want to add the record (22,22,33,44), then also add (33,22,33,44), also (44,22,33,44), etc. i use the innoDB version. is there any easy sql that does this?
View Replies !
How To INSERT A Record When NOT EXISTS
How to INSERT a Record when NOT EXISTS My current code has a compile error: DECLARE MyVariable integer; IF NOT EXISTS (SELECT * FROM MyTable WHERE RecordID = 1) THEN INSERT INTO MyTable (Column1) VALUES ('Some value') SET MyVariable = LAST_INSERT_ID() END IF MySQL tells me that this has a syntax error. Help! How can i implement this properly? What's the best way to do it?
View Replies !
Insert Null Value Into Record
I would appreciate any help you can give. I have a table which includes INT fields which can be null. Using a PHP file, the site administrator can update a record. However if she leaves a field blank which corresponds to INT column which can be NULL, The value of "0" is added to the data base instead of leaving it as NULL. Here is the code I use to create the table: ...
View Replies !
Insert Record At A Specified Position
I have a table having 2 columns ie, 1. id : primary key, auto increment 2. name I want to insert a new record at a specified posion in the existing table having some records Suppose I hvae 5 records and I want to insert a new record at position 3. How can I do that in MYSQL . I'm using version 4.
View Replies !
Insert Record And Return Id
lets say i have my db setup as such user id | name | email | default_pic picture id | user_id | name if each user can have multiple pictures with one being their default it would be helpful to insert their account and default picture into the database and update their pic. right now im doing something like this //insert user into db //insert picture into dabase //select most recent picture //update default_pic with most recent picture is there any way to do this //insert picture into db and return the id just created //insert user into database with pics id we just got running these 4 sql commands as shown above seems to be slowing my page down a great deal.
View Replies !
Can I Insert A Record Via Email?
I have a form which is filled out, when it is submitted I get sent an email in the format of a pre-filled html form, In the email I have a button which inserts the dataset into the mysql table. I get the email perfectly, with the data the user entered in a form, however when I press the submit button (which takes my to the page to insert into the table this happens (screenshot) As you can see, none of the variables seem to be passed over (The forms method is POST) and each variable goes iknto the POST array PHP <?php$IP = $_POST["IP"];$Name = $_POST["Name"];$Email = $_POST["Email"];$Website = $_POST["Website"];$Phone = $_POST["Phone"];$Display_Email = $_POST["Display_Email"];$Website_URL = $_POST["Website_URL"];$Display_Phone = $_POST["Display_Phone"];$Age = $_POST["Age"];$Hourly_Rate = $_POST["Rate"];$Location = $_POST["Location"];$Other_Location = $_POST["Other_Location"];$Eye = $_POST["Eye"];$Hair_Color = $_POST["Hair_Color"];$Build = $_POST["Build"];$Hair_Length = $_POST["Hair_Length"];...$Services_Offered = $_POST["Offered_Service"];$Rules = $_POST["Rules"];$sql = "INSERT INTO PROVIDER (IP_Address, Name, Email, Website, Phone, Display_Email, Website_URL, Display_Phone, Age, Eye, Rate, Location, Other_Location, Hair_Color, Build, Hair_Length, Bust, Hips, Cup, Waist, Height, Availability, Grooming, Services, Rules)VALUES ('$IP', '$Name', '$Email', '$Website', '$Phone', '$Display_Email', '$Website_URL', '$Display_Phone','$Age', '$Eye', '$Hourly_Rate', '$Location', '$Other_Location', '$Hair_Color', '$Build', '$Hair_Length', '$Bust', '$Hips', '$Cup','$Waist', '$Height', '$Availability', '$Grooming', '$Services_Offered','$Rules')";echo $sql;mysql_query($sql) or die(mysql_error());echo "<h1>Updated, approved.</h1>";?>
View Replies !
Updating Column With Sumned Values For Each Record In Table
I have most of the query formulated, but am stuck on the portion where I need to specify which records to update. I want to update all the records in the table. How can I specify the request_id to be the same in both the select and update statement? Here's the query I'm working on: Code: update enhancementrequests2 set hardBenefitEffortSavings = (SELECT hardBenefitEffortSavings + hardBenefitLaborSavings + hardBenefitHardwareSoftwareSavings + hardBenefitOtherSavings AS hbTotal FROM enhancementrequests where request_id = 1), hardBenefitLaborSavings = NULL, hardBenefitHardwareSoftwareSavings = NULL, hardBenefitOtherSavings = NULL where request_id = 1 In my example I have specified that request_id = 1, but I want request_id to match each row of the table in turn. I hope that makes sense.
View Replies !
Check If A Record Exists In The Last 30 Records
I want to check if a record exists in the last 30 records that were added. For example, i have a table wich contains about 34000 records by now. But i want to check if the songID is in the last 30 records. Now i select the last 30 records and put them in an array, and let php check if this songID is in the array. But it must be much easier if mysql this does!
View Replies !
Storing Many Records Associated To Single Record
Let's say that you have a table of car models and each model is associated to a make in another table through a foreign key. Now, in another table you need to store all of the models that the customer has selected. Is it correct to store them in a table like this (periods used for spacing): user_id, model_id ...1........1 ...1........2 ...1........4 ...2........2 ...2........23 ...3........9 etc. or is there a way to serialize this data that's easily searchable like this? user_id, models ...1.....1,2,4 ...2......2,23 ...3.......9 etc. Which is considered the best practice? I need to be able to quickly search for all people who have selected model_id "2" or model_id "23".
View Replies !
Finding Records That Are *similar* To Your Record
I want to enable users to create groups in my website. However; i also want to check that lots of users dont go and create lots of similar groups because they are unaware that other groups exist. So: i need to be able to find groups in my database which have names (if any) which are similar to the one that the user has entered in. Is there any existing MySQL function for doing this? One thing which i have found is the liebenstein distance, but it appears that you have to install that function as a user function or something...which seems a little beyond me right now. Is there perhaps another way of finding the similarity between two strings?
View Replies !
Finding Primary Key Of Record After INSERT
I have a mysql db with with autoincrementing primary keys in each table. I want to allow a user to add a record using a php webpage. Once the page is added how can I re SELECT that record (for the user to verify) if I don't know what the primary key was when it was inserted?
View Replies !
Insert A New Record Using Data From Another Table
tblPurchases purchasesID quantity model tblProducts productsID quantity model In my database some products have been sold before they were purchased so that the quantity in tblProducts becomes a negative number. How can I create a INSERT INTO statement that would allow me to enter a new purchase for any products with a negative quantity. It should also purchase the correct quantity to bring the value on hand to 0.
View Replies !
|
TRACKER
