Update All Rows In A Column
I'm a complete newbie but I'm trying to do something that I would think would be easy. In my table I have a column LICLEVEL with values of Club, Expert and Elite. I need to tack on "USA Cycling - " in front of that value, so that if the current data is "Elite" after the update it will say "USA Cycling - Elite". With 800 records to update, I'm hoping there's a fairly easy way to achieve this with SQL (or PHP).
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
Set Column Value In X Rows
I have a database of URLs that is changing constantly. The fields are id (autoincrement), URL, valid, time, new. When a URL is added, valid is set to 0 and new is set to 1. I have an admin panel, where the admin can choose to activate any amount of URLs that he wishes. So what would the code be for that? I'm a mediocre mySQL guy, so this is really causing me some problems. If the admin enters 10 into the form, then I need to take 10 URLs where valid=0 and new=1 and set them to valid=1 and new=0.
Two Rows From Same Column
I have a forum table that contains the last user that posted on the forum and the posts original user. I want to get both of those users names and IDs from the user table in one SQL query
Multiple Update Of 21 Rows
I have a database table which holds price ranges for various classes of hire car and various hire periods. There are 7 different hire classes and 3 different hire periods. 21 records in total. Heres an example of what the table contains: ID......HIRE PERIOD.....HIRE GROUP....PRICE 1..........14.....................a...............25.00 2..........28.....................c...............15.00 The rows are shown in an editable form, which is basically a grid of text fields. I need to figure out the most efficient way of updating all the records when the grid is edited. Do I need to perform 21 individual updates? as I'm worried that this will be too cumbersome when there are multiple users. I thought of one way which involves having a hidden field for each row, called 1,2,3 etc, each with a value of eg. 14,a,25.00 as a comma seperated list. Then I split the post values into arrays and perform various updates. Seems a bit clunky though,
Update Multiple Rows
I have about 20 rows that need to updated together and would rather not have 20 seperate update lines but am not sure what else to do. At the moment it looks something like this: UPDATE movies SET mon='10pm', tues='10pm', wed='11pm' WHERE movie_id='19'... UPDATE movies SET mon='9pm', tues='11pm', wed='4pm' WHERE movie_id='37'... UPDATE movies SET mon='8am', tues='1pm', wed='5pm' WHERE movie_id='19'... There is no logical order to the movie_id's they are selected from the movies table using specific criteria.
Update One Field In All Rows
I have a mysql database which contains 24,000 rows. I added a field called "RECALL", which contain null values. What I'm trying to do is fill that field in all 24,000 rows with the value "N".
Update Yields Zero Rows
I have a PHP query that is suppsed to update a table. When I run it mysql_affected_rows returns a zero. Unfortunately there are no errors I can find to see why. If the query "update" fails like this how do I find out the reason?
Update Multiple Rows
I have an array that I need to put into an existing db column using a perl foreach loop. I can do this using an INSERT statement but the script will be run every day and the source array will be changing as it is based on directory contents so I need to UPDATE vs. INSERT With the INSERT statment the script places each array element on a new row of the db as it should but when I try to use an UPDATE statement, I get a single array element written over and over on each row of the db. Code:
Update All Rows Conditionally
Having a bit of trouble getting this syntax right. It needs to go through each and every row, read a field and if that field = a certain text, update another field in that same row. if(locate('text to locate' , `dbName`.`tblName`.`fld1`) , update `dbName`.`tblName` set `dbName`.`tblName`.`fld2` = 'new text') .
Rows Changed In Update SQL
Is there a way to find out how many rows got updated when i execute a update stmt. I would like to get the updated rows count in SQL. Let me know and Thanks in advance. update stmt where clause; now how can i get the count of rows that got updated.
Select 5 Rows Per Column Value
ID | NAME | EMAIL | CITY | How could i select 5 rows for each city value? In other words, if there are rows containing St. Luis, NY, LA - I want to select 5 users from St L, 5 from NY and 5 from LA.
Update A Column
I am using PHPmyAdmin to update a database because I cannot connect with the cool tools you guys have here. So in an effort to just get this one simple take done and overwith I need to know what query to run. I need to change the table jos_dir_listings where the column says premium from 0's to 1's however, I do not know the proper query to run. when I run it in the Auto editor that phpMyadmin provides I get a Syntax error.
UPDATE Column
I want to updata a column in table1 when a column matches a column in another table. I have tried this: "UPDATE newsletter SET status=? WHERE newsletter.email=blacklist.email" and also tried: "UPDATE newsletter SET status=? WHERE newsletter.email LIKE '%blacklist.email%'" Both return error message: "Unknown table 'blacklist' in where clause at...." Why doesent this work? and is there a better way to to this without using a line by line comparison in the script like this slow Perl script: $dbh=DBI->connect($db1_name,$db1_user,$db1_pass); $sth1 = $dbh->prepare("SELECT email FROM blacklist"); $sth1->execute(); while (($blackmail)=$sth1->fetchrow_array()){ $sth2 = $dbh->prepare("UPDATE newsletter SET status=? WHERE email LIKE '%$blackmail%'"); $sth2->execute(0); $sth2->finish(); } $sth1->finish(); $dbh->disconnect();
Update A Column
I have 117,000 records in a table, and unfortunately I'm deciding "after the fact" to add a n auto_increment id column. How do I update the existing records with serial numbers?
Update One Column
I am using PHP/MySQL. I have the following query: CODE$usql = 'UPDATE `productsd` SET pdQtyOnh = pdQtyOnh - ' . $qty . ' WHERE pdID = ' . $key . ' LIMIT 1;';
MySQL Update Speed With 10K+ Of Rows
I Have table called `users` which have 10K+ rows. Each user have a fields called `gold` and `level`. Now each user should get "pow(1.25, level)*100" gold per/hour and it should divide so that users will get 1 gold per time unit. example: user level is 7 and he should get 1.25^7 * 100 gold = 476 gold per hour (1 gold every 7.5 seconds). Now I have a problem. I can't update all users at once every second because it would create too much load for server (mysql load was 100% if I did it). Even if I would do this update in every 7 seconds, it will be too much load for the server. maybe we can determine user gold amount so: user gold = user gold + (seconds_after_last_update/3600); and do update every prime hour? but what happens the when user wants to spend this gold? My mysql-updateing daemon is running in infinite loop doing 1 action every second.
SSH: Select Database And Update Rows
I'm trying to update a few rows in my database via SSH. I have multiple databases on my server and I need to know what I'm doing wrong. I login via SSH and enter $: mysql -u root -p I want to select the database and then run my update command, but when I type. > select [db_name] It just jumps to a new line without any prompt. Can anyone tell me really quick when they do to select a database?
How To Update Multiple Rows With One Query?
I am using PHP/MySQL and need to update 7 rows with one query. Can someone tell me how to do the following so it will update the row for each day of the week? (This obviously doesn't work) $sql = "UPDATE business_hours SET hours='$sunday' WHERE id='$id' AND day='sunday' AND SET hours='$monday' WHERE id='$id' AND day='monday'";
Update Script, But Check If The Value Is The Same On Other Rows
I have a nice user script. In a particular section there is the ability to edit the users on the databse. What I am concerned about is that people may edit the username or email and find its the same username or email that another user has, this can screw up the login system especially two usernames being the same. So is it possible to make it so that if the email/username is the same in another row to give an error, Please note that the input fields for their email and username are already filled in so if they dont come to edit it them they will still output there original username/address.
MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:
Update Multiple Rows Within One Table
I have a Dynamically created formfield. Tgis field shows the attached devices off a selected device (for instance you have a pc and in the formfields it shows two attached monitors). The monitors however are in the same database table. (with an other device_id ofcourse). The page with the pc info is shown by using the device_id in the URL. What I want to do is update some fields in this table for the pc but also for the attached devices.... When I do it like this: UPDATE dev SET .... etc. WHERE device_id IN ('#FORM.koppel_2#') It selects both device id's like this: WHERE dev_volgnr IN ('602,603') This doesn't work! However in this example the table should be updated for the devices with device_id 602 and 603. Is this possible? How would I do that?
Import A Csv To Update Only Certain Rows Of Data?
I have an existing mysql table and a csv. I want to import the csv into the table, but it is at a different sort order. There are too many records to sort my csv by hand to match. There is a common 'cd_code' field that both have. Is it possible to import a csv to the table so that each row of the csv is inserted in the correct row of the table, based on the common 'cd_code' field? For example, if I sorted by cd_code asc, it would match the csv, but how to I import the csv into a particular sort?
Wanting Distinct Rows (by Only One Column), Not Sure How To Do It
I have a table `logins` with id, name, ip, mac, date ip and mac are both tracking information for name and I'm trying to write a query that, when given a name, selects all names that person has logged in with by cross-checking their ip and mac with every record we have for that name I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name. SELECT * FROM `login_history` WHERE `ip` IN ( SELECT `ip` FROM `login_history` WHERE `name` = 'Name' ) OR `mac` IN ( SELECT `mac` FROM `login_history` WHERE `name` = 'Name' )
Select Rows With Common Column
I'm trying to figure out a simple query, and just can't manage it. I have table like so: Name-----------Programme-----------Channel Dave Simpsons Sky One Dave 24 Sky One Dave Family Guy BBC2 Nicola CSI Hallmark Nicola Friends E4 What I want to do is select everything from the table. But, I would like to group them so that the rows returned look like Row 1:Dave, Simpsons, Sky One, 23, Sky One, Family Guy, BBC2; Row 2:Nicola, CSI, Hallmark, Friends, E4; Is this possible, or just plane stupid? I take it I would have to create a temporary table?
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.
Find Rows With Same Info In A Column
How can I query so that I find all the rows where the information in the same columns is the same? So that I can get the information from both rows?
Concatenate Multiple Rows On Same Column
I have the following table structure holding product long descriptions: Part No(pn)Sequence (seq)Long Description (long_desc) --------------- --------------- ---------------------------------------- HL145010This is part of a long description and HL145020it overlaps onto several lines. I'm HL145030having difficulty writing the SQL that HL145040is needed to make this work... I need to write SQL that will give me the following output: Part NoLong Description --------------- -------------------------------------------------------- HL1450This is part of a long description and it overlaps onto several lines. I'm having difficulty writing the SQL that is needed to make this work... (Essentially it a GROUP BY on the part number with an ORDER by on the sequence field). However, I know how to concatenate multiple columns on the same row (using the CONCAT or CONCAT_WS functions) but I'm at a loss over how to concatenate the same column over multiple _rows_ I am aware of the GROUP_CONCAT function, but we are currently running MySQL 4.0.13 (this functionality is only available on a vers > 4.1)
Update One Column Based On Another
I have two tables. One set up like this: id item_id group_id userid outcome settled The second is userid yes no I would like to look at the first table and find all rows that have a specific group_id, then update the userid yes/no based on the outcome on that row. Outcome Y then add 1 to the yes column of that userid, outcome N then add 1 to the no column of the userid. Is this possible in one SQL statement?
How To Update Values In A Column
I have a price list that needs increasing by 10%. Is there any way to do an update commmand and multiply all values in the column by 1.1 to achieve this? I've tried various commands but to no avail.
Update Values In Column
i have a table with about 500 rows, i need to UPDATE the city column in each row. I want to remove a comma (",") a space (" ") and the 2 letter state abv ("FL") from all values in the column (named 'city'). example: "Miami, FL" should become "Miami"
Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin. THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN: $sql = "update table set color='red' where id='1';"; $sql_do = mysql_query($sql); THIS QUERY WORKS ONLY IN PHPMYADMIN: $sql = "update table set color='red' where id='1'; update table set color='green' where id='2';"; $sql_do = mysql_query($sql); I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.
The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people The table is quite large (300,000 rows) I need to write code that update many of the rows in the table Here is what I need to do Two of the fields that I store about each person are: location and country The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements I cannot use LOAD DATA as this is product database Is there a way to update multiple rows (each with a different value and condition in a single query?
How To Update/Delete Bunch Of Rows Quickly?
I have a textarea that allow user to edit a bunch of rows of data quickly. It either add/delete/update rows. How do I do it quickly? The db table has rows of names. e.g. the db table has: car |Susan dog |John monkey |Chris John enters: plane boat house After processing, the db table should say car |Susan plane |John boat |John house |John monkey |Chris How do I update the db table quickly? If I use update query, it will update John's rows but there is no rows to update? There are only rows to add and delete at this time. I don't want to delete all records of John every time John enters the data, because a user can potentially have a lot of rows in the table.
Count Or Select Only The Populated Rows In A Column?
I'm trying to COUNT the number of populated rows within a column named, 'column_03', but first instead of using COUNT i'm using SELECT to see where the numbers are coming from. lets say i use the following code... SELECT column_03 FROM table_name WHERE table_name.column_01 = 'IN'' AND table.name.column_02 ='GENER" the result is a total number of 10 records. i can see that 'column_03' has 6 rows that are populated and 4 rows are empty. now what i want to do is select only the 6 rows that are popluated, once i now how to do that i can then use COUNT. Any suggestions?
Adding ON UPDATE CURRENT_TIMESTAMP To A Column
I'm trying to add ON UPDATE CURRENT_TIMESTAMP to a TIMESTAMP column but can't figure out the syntax. I tried various SQL strings including: ALTER TABLE `Content_elements` ALTER `last_updated` SET ON UPDATE CURRENT_TIMESTAMP
How Do I Update A Column Value Depending On A Condition...
I want to update the value in a column only if it is less than a supplied value. for example if I had a table called autionItems containing a column called offers, I have got a new offer and need to update the autionItems table only if the new offer is greater than the existing value.
UPDATE Changes Unreferenced TIMESTAMP Column
I have a table demo defined like this: >>create table DEMO (ID BIGINT AUTO_INCREMENT PRIMARY KEY , TIMESTAMP TIMESTAMP, STATUS VARCHAR(10)); I insert a couple of values: >> 1, 2007-08-31 10:10:10 >> 2, 2007-08-31 10:10:11 >> 3, 2007-08-31 10:10:12 Then I change the STATUS field with an UPDATE query, even the TIMESTAMP column changest to current_timestamp. UPDATE demo SET status="hello" WHERE id=2; >> 1, 2007-08-31 10:10:10 >> 2, 2007-08-31 09:06:45, hello >> 3, 2007-08-31 10:10:12 I don't understand this behaviour. I havent references the TIMESTAMP column and hence expect it to be unmodified. Can it be that the TIMESTAMP has a DEFAULT_VALUE specification? Probably not, because if I inser another column (DEFVAL INT DEFAULT 5) that colum does not change when updateing the STATUS field - only the TIMESTAMP does.
Update Just Time In Datetime Column
I have a datetime column called foo, it currently has dates in it with all times set to "00:00:00". I would like sql to update all the rows and set the time to "15:15:00" but leave the dates as they are today.
Update Using Like Wildcard And Table Column
I'm trying to update table 1 with data from table 2 where the data in table 1 is like the data in table 2. I need to use the % wildcard but I can't seem to get it to work using a column name. Is this possible, or am I going about this the wrong way. Here is the statement I have, it only updates records that match exactly. update crowley_preowned,models set crowley_preowned.po_display_model = models.model where crowley_preowned.po_model like models.model po_models contains say PT CRUISER LXI, models.model contains PT CRUISER. I want to update po_display_models with PT Cruiser from model for any record that starts with PT Cruiser.
Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0: idcol lft (unique) 1 1 2 2 3 3 4 4 -> when I try to update the lft column using : UPDATE unique_example set lft = lft +1 where lft > 1; I get following error message : Duplicate entry '3' for key 2 Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:
Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively. When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause Is there any way around this problem?
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
Update Column In Table A With The Count Of Another Table
Having my SQL-background in Oracle I'm looking for the MySQL equivalent for: update tableA set column1=(select count(*) from tableB where tableA.key= tableB.key) cannot find anything similar in the manual, but probably overlooking something.
|
TRACKER
