Help With Simple SQL Statement To Update Zipcodes
I have a varchar(5) field in a database called 'zip_code'. I imported some zipcodes from an Excel spreadsheet, but the spreadsheet had truncated any zipcode that started with a "0" and removed the zero, so the correct zipcode of "08993" was incorrectly placed into the record as "8993".
Would someone help me write a SQL statement to run on the field zip_code that would look at the data, if it was four characters long, add a "0" to the start and keep the remaining four characters in place?
View Complete Forum Thread with Replies
Related Forum Messages:
Simple IF THEN Statement
Ok all i'm trying to do it left join a table on another and order by the data in the joined table. Meaning that since it's left joined there is the possability that there will be no data in that table. If not then i'd like to make the order by field contain a number in order to preserve ordering. The entries with no information from table B would end up at the bottom of the ordered list. Table A id | someInfo | moreInfo | 1 blah blah 2 Foo Bar Table B id | tableA_id | someInfo | order_column | 1 2 Weee 1 Now with the above if i call and join the tables with the following, select tableA.someInfo, tableA.moreInfo, tableB.someInfo from tableA left outer join tableB on tableA.id=tableB.tableA_id order by tableB.order_column ASC; With this i get the following order, blah Foo Where blah has no entry in tableB. Since there is no entry in tableB i need to it end up at the bottom of the list not the top. But i understand that since there is no entry it is showing the correct order since nothing in ascending order is higher then 1 (similar to 0 being the first number). But i need to be able to throw an if statement in there, something like the following, IF tableB.order_column < 1 THEN tableB.order_column = 9999999 The problem is that i'm not sure how to add this to the above query. I mean i've never added an if statement to a direct query before and am unable to create or work with views/stored procs on this server.
View Replies !
Simple Case Statement
I am trying to execute a statement that works fine in ms sql but will not work in mysql. There are three tables. user (user_sid, user_name, user_email), file (file_sid, file_name, file_desc), file_access (file_sid, user_sid). Here is what I want: Given a particular file_sid return all users with an extra column (1,0) inticated whether or not there was a match in the file_access table for given file_sid. here is what I attempted that DOES work in mssql. select case when not (select file_access.user_sid from file_access where file_access.file_sid=1 and file_access.user_sid=user.user_sid) is null then 1 else 0 end as has_access, user.* from user
View Replies !
Simple Join Statement
I seem to be a complete idiot when it comes to join statements. Here is what I am trying to do...two tables - TABLE tags COLUMNS -id -tag TABLE tag_assoc -id -article_id -tag_id I want to get a list of the tags for a specific article ID. The first table is simply a list of all the tags, while the second table lists relationships between an article and a tag. An article can have many tags.
View Replies !
Simple Select Online Users Statement...
I want to select all users in the active_users table. In the active_users table there are two fields, user_id and timestamp. I want to pull them out, but i want to pull out the user_name from the users table where user_id equals the user_id from the active users table aswell.
View Replies !
A Simple Update
What is wrong with my code? UPDATE TABLE Ahs_cat SET Ahs_cat.Master_ID to Ahs_master.ID WHERE Ahs_cat.ID = Ahs_master.cat I get an error every time, but to me it looks like how the manual is telling me to do it.
View Replies !
Simple Insert And Update
Im having a rather irritating problem..because its so simple and I just cant figure it out.In my database I have some company info and the primary key "companyID" is used to reference it in several other tables. however in phpmyadmin I cannot figure out how to set it to automatically generate when I create a new company in the company table and then propogate to all the tables it links too.
View Replies !
Simple Question About UPDATE Syntax
I have an INSERT query that looks like this : insert into student(student_id, student_name, school_id) values(��','CHAN, DAVID',Ƌ'); Am I able to create a similar query for UPDATE? Something like update student set(student_name, school_id) where student_id = 5 values(��','Kelly, Ned'); I wish to achieve a query where the new values are seperate from the query. Seeing as I cant find this anywhere online and I get errors when I test it, I am supposing not. If anyone could tell me for sure, I'd appreciate it!
View Replies !
Simple Update Just Not Working /banghead
Background: I've been around the web for quite some time, but am new to PHP/MySQL. I recently purchased a zip code database for an upcoming project I'm starting here, and for some reason the CSV format turned all the zipcodes that start with a zero into 4 digits. No biggie, after hours of wrestling with cpanel MySQL import, then giving up and moving on to modifying the bigdump script I FINALLY got the import done. Now I'm trying to correct the 4 digit zip codes back into 5 digit ones. Table name is ZipCodes, column name is ZipCode (is varchar(5) column). Here's the code I'm trying to use to update things, but it just does not perform the update for some reason. update ZipCodes set ZipCode = Ɔ' + ZipCode where char_length(ZipCode) = 4; I've spent a few hours now reading up trying to figure out what the hell I'm doing wrong, but to no avail. I'm sure the fact that I'm a newb at MySQL isn't helping, but is it something obvious that I'm just to dumb to see at this point?
View Replies !
SQL-Update-Statement
I have to update all UA-fields in the table idrz_kennzahl with primary key idrz, where the same idrz in the table per_verwaltung with the field Amt=12 is.I designed following statement but it doesn't work: update idrz_kennzahl set UA=1234 where idrz_kennzahl.idrz =(SELECT per_verwaltung.idrz from per_verwaltung where per_verwaltung.Amt=12
View Replies !
Update With If Statement
I'm trying a statement which I cannot figure out what I'm doing wrong on. I've tried every combination that I can think of and stared myself blue with no success. update cdr set Cost=if(mod(BillSec,6)=0,int(BillSec/6),int(BillSec/6)+1) The idea is to update Cost with a value depending on the result of a formula. It's an empty field which gets a computed value from other fields. cdr is the table.
View Replies !
Update Statement
I am currently updating a table and increasing a column value by 1, the problem is that he don't increase 1 but it does increase the double of the value specified. Why does it performs the update like this? And how can i solve this problem? SQL Statement Syntax used: UPDATE table_name SET column_name=column_name+1 WHERE id=1 Version of MySQL: 4.1.7 for Windows installed in Windows 2003 Server.
View Replies !
Long Old UPDATE Statement
I have the following SQL which gives me the row Im after SELECT * FROM staff, staffemails WHERE staff.users_username='$user' and staff.staff_id = staffemails.staff_id thing is I now want to perform an update on the result of this query. Ive tried UPDATE staffemails SET is_registered ='0' WHERE staff.users_username = '$user' and staffemails.staff_id = staff.staff_id .
View Replies !
Update Statement Not Working
Why does this simple update statement not work with a mysql version 3.23.49, it does work when using 4.0.24..... ? UPDATE listitem,vjbase,list SET listitem.showsec = 7 WHERE listitem.id_vjbase = vjbase.vjbase_id AND listitem.id_vjbase = 3 AND vjbase.id_viduser = 61000 AND listitem.id_list = list.list_id AND listitem.id_list = 1 AND list.id_viduser = 61000
View Replies !
UPDATE Statement Doesn't Work
TABLE - abc ID NAME ___________ 1 hello 12 abc UPDATE `abc` SET `ID`='5' AND `NAME`='hello' WHERE `ID`='1' AND `NAME`='hello' wherenver I try to update this table using above sql statement, it doesn't work. I just get "Row match:1 Changed:0" message.
View Replies !
Very Slow Update Statement
I am having an issue with an UPDATE statement that takes a very long time. I am using 1 table in a schema to update another table in another schema. Below are the create statements and the update statment I am using. Table and column names have been changed to protect the innocent :) Code:
View Replies !
Special UPDATE Statement
UPDATE some_table st SET some_page_no=((select count(*) FROM some_table WHERE some_table_id<st.some_table_id and some_id=st.some_id and some_type='OK') div 10)+1 WHERE some_id=345 and some_type='OK' I want to use the above to make a fast page index (10 on each page). I had something similar in mssql and it worked, but in myssql I get this error: "You can't specify target table 'some_table' for update in FROM clause" What have I done wrong or can the above simply not be done?
View Replies !
UPDATE / JOIN STATEMENT
i have problems after updating mysql from 4.x to 5.0.19. This statement should update about 3000 rows but it only does 15 but there are definitely another 2000 rows with an older date and matching nr! UPDATE tab1,tab2 SET tab2.newdate = NOW() WHERE tab1.nr=tab2.nr; I tried the variation with inner joining tab1 as well. same result. Does anyone has an idea solving this problem? Perhaps it´s a bug?
View Replies !
Select + Update In One Statement
I have these statements. $selectdata = "SELECT * FROM imagetrack WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'"; $query = mysql_query($selectdata) or die(mysql_error()); $update = "UPDATE imagetrack SET seen = seen+1 WHERE fimsid = ".$_GET["fimsid"]." AND name = '".$_GET["service"] . "'"; Basically i was wondering if there was a statement where i can select everything and update the seen in one statement.
View Replies !
Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1 data: $min_time, $max_time, $time, $attempts so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database. "UPDATE mytable SET avg=(avg*attempts+$time) DEV (attempts+1), min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time?? **and similar with max_time .
View Replies !
Update Statement Problem? Plz Help.
I've a simple doubt of whether can or can't i update two tables by using joins as below. the below update statement matched 2 rows one each for two tables but didn't update. mysql> update audio join pcr_info on audio.audio_sid=pcr_info.pcr_sid set audio. audio_pid=4896,audio.audio_prgm_num=3,audio.audio_type="MPEGLayer1",audio.audio_ sr=44.1,audio.audio_mode="1_0",audio.audio_bitrate=320,pcr_info.pcr_pid=49 where pcr_info.pcr_prgm_num=3 and pcr_info.pcr_sid="2501" and audio.audio_pid=4896 an d pcr_info.pcr_pid=49; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0
View Replies !
DELETE,UPDATE Or Other Statement In WHERE
I would like to let my admins write WHERE statements. . In panel where you can see all users of my site, admin should have input where he can put some filters in WHERE statment. E.G. standard query is SELECT login FROM USERS . and admin would write age > 18 and male = 'f' . php script would conact it and it would execute SELECT login FROM users WHERE age > 18 and male = 'f' I have question about security of this solution. Is possible to put in WHERE statement sobe dangeurous statemetns like DELETE of UPDATE?
View Replies !
Conditional Update Statement
I have a table with a field of ID numbers. I want to update this field and replace the ID numbers. For example I want every record that currently has the ID number 2,5,6,or 9 to be replaced with a 1. Every record with 1 or 4 to be replaced with 2 ... I can't seem to form the update statement to do this. I can do them one at a time but this won't work because if I change 2,5,6, and 9 to 1 then when I run then update for 1 and 4 it will change them all. Does this make any sense? I want some way to be able to use a condition...if id=2 or 4 or 6 set it to 1 and if id= 1 or 4 set it to 2. My failed attempt: mysql> update client_software set software_id=1 WHERE (software_id=2 or software_id=5 or software_id=6 or software_id=9) AND software_id=2 WHERE (software_id=3 or software_id=7 or software_id=8) AND software_id=3 WHERE (software_id=1 or software_id=4);
View Replies !
Insert Or Update In One Statement
I sometimes find myself writing PHP code that checks for a value in a table and updates it with a new value if it exists, or insert it into the table if it doesnt. Is there any way to do this with one sql statement?
View Replies !
Conditional Update Statement
MySQL Code: UPDATE   tournamentsSET   tournament_player1 = IF (tournament_player1 = 5, 1, 0),    tournament_player2 = IF (tournament_player2 = 5, 1, 0),   tournament_player3 = IF (tournament_player3 = 5, 1, 0),   tournament_player4 = IF (tournament_player4 = 5, 1, 0) WHERE  tournament_time = 1 AND tournament_game = 1 ....
View Replies !
Convert SELECT Statement To UPDATE
How do I convert the following SELECT statement to UPDATE statement? If I'm not mistaken, UPDATE statement could not involve more than one table. SELECT * FROM F0116 LEFT JOIN F0101 ON F0116.ALAN8=F0101.ABAN8 WHERE F0116.ALCTR='' AND F0101.ABMCU='1'
View Replies !
Update Statement Not Working On Server
this works on my machine but not where my site is hosted. They do use an older version of mysql could that be problem update inv, newprice set inv.cost = newprice.price where inv.itemcode = newprice.itemcode works fine on my machine her ebut on server i get error..
View Replies !
Update Statement :: Affected Rows: 0
UPDATE `zipLocations` SET `z_zipCode` = '06001' WHERE `z_zipCode` =6001 LIMIT 1 ; Field Type Attributes Null Default Extra Action z_zipCode int(5) No 0 z_cityName varchar(50) No s_stateAbbr char(2) No z_countyName varchar(50) No z_areaCodes varchar(50) No I even went through phpmyadmin found the row clicked on the edit button for that row and updated it. and it still doesn't update.. im' thinking it has to do with the data type being INT... Why doesn't it update it ?.. It says it was successful. Affected rows: 0 (Query took 0.0002 sec)
View Replies !
CASE Statement In Update Command
I'm trying to update one table (tbl_usage) based on information from another table (tbl_subscriptions) joined by the userID. The usage table tracks the number of times a user hits a page, and decrements the count each time. When the user has a zero count, they are redirected to a warning page. Each week the count is reset depending on what type of license they have (licenseType) and how many of those licenses they've bought (numLicenses). A group license (G) gives them 100 hits, and an individual license (I) gives them 5 hits. I'm trying to use a case statement to perform this count reset operation, but I can't seem to get it to work. The following case statement works just fine: Code:
View Replies !
Convert A Nested Update Statement
i have a nested update statement in my sql server as follows UPDATE DIM_EVENT . SET INVOICE_AMOUNT = (SELECT SUM(INV.INVOICE_AMOUNT_TOTAL) FROM INVOICE INV WHERE INV.INVOICE_REFERENCE_NUMBER = DIM_EVENT.OBJECT_ID AND INV.INVOICE_AMOUNT_TOTAL IS NOT NULL) WHERE DIM_EVENT.EVENT_TYPE_CODE IN ('OFULN', 'OAUTH', 'CRDRL'); what is the equivalent in MySQL.
View Replies !
Update Statement :: Data From One Table To Other
I have two tables, and I want to take data from one table, and overwrite it onto the second. Unfortunately, my SQL is pretty weak. The first table has a mediumtext column and a id (primary key) column. The second has an id (the same as the first), a number, and a mediumtext column. I want to take the contents of the mediumtext field with the highest number and replace the contents of the field in the first column with it with the same id. My instinct is to do it this way: update forum1post p set p.pagetext = (select query1.message from (select pe.message, max(pe.editnum) from forum1post_edits pe group by p.postid) query1) where p.pagetext = 'Why did you all flame my topic. Screw your ****ing forums.' and p.postid = pe.postid However, that does not work, as pe is not recognized.
View Replies !
QUERY One Table And Update Another In ONE Statement?
ZIP_CODE: zip_code latitude longitude customers: customer_id ... zip_code latitude longitude The lat and long in the customers table is blank. I want to lookup customers' zip code in the ZIP_CODE, get the related lat & long and update the customers table with that information. The result being that after this query is run, the customers table now has lat & long data.
View Replies !
Gegraphic Point.longitude-zipcodes.longitude
I have two tables: first "points" with id, latitude and longitude, second "zipcodes" with id, zip, latitude, longitude. Id like to ask the query which would give me a table with id, zip where id is the id of the point from points table and zip is the closest zip to this point. I know that i should use something like: min(abs(point.longitude-zipcodes.longitude)) but i have no clue how to as the full query.
View Replies !
MySql Update Or Insert Statement To Modify A Word In All Caps
I have a field in my database called Subtopic. In this field I have a word LITERATURE in all caps. I need to change the word LITERATURE to LITERATURE followed by a colon then an empty space. Can someone give me the MySql statement I can paste into phpMyAdmin to make this change? Note: The Subtopic field contains the word "literature" but not in all caps because the Subtopic field is part the database that is an encyclopedia. Therefore, I want to change ONLY the word LITERATURE if the word LITERATURE is in all caps.
View Replies !
Simple (?) AND
SELECT * FROM table WHERE (date_col >= '2005-09-26' AND time_col >= '18:00:00') AND (date_col <= '2005-09-27' AND time_col <= '18:00:00') I get two results from this query, one for 18:00:00 from each day, when what I want to get is from 18:00:00 and onwards for the first day and 18:00:00 and before for the second day.Pretty much my query is behaving like this... SELECT * FROM table WHERE (date_col >= '2005-09-26' AND date_col <= '2005-09-27') AND time_col = '18:00:00') Another option is to combine date_col and time_col into a query like... SELECT * FROM table WHERE date_col + time_col >= '2005-09-26 18:00:00' AND date_col + time_col <= '2005-09-27' 18:00:00' Less messy but I'm not sure how to combine the columns for the query as above.
View Replies !
Simple Error
I keep getting a syntax error, when trying to run this query (perl script): SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits Ive tried with brackets around the last SELECT statement (Essentially, the tables hits, new_hits and temp_hits hold the same columns, what I want to do is extract any rows from new_hits, where the url field is not found in the hits table...).
View Replies !
Simple Join?
I need to find all the groupID's that employee ID=123 doesn't belong to given the following table structure: EmployeeGroup ------------------------ groupID groupName EmployeeGroupMap ---------------------------- groupID employeeID Ideas?
View Replies !
Simple Incrementing
I have a field within my mysql database, this field, ContractNo, is the primary key. I have auto-increment set up to run on this field. However, it only starts from 0. The field is of type int. The value I wish to start the increment from is 24001000. Is it possible to assign this value as a starter value for my field to increment +1 everytime. I'm using navicat and everytime I place 24000000 as the default value, it always reverts it back to being blank.
View Replies !
Simple Timestamp
I am working on a new DB I have built, and one of the fields I made is the MySQL timestamp.I "thought" this field would autopopulate when a new record is created, and not change after that unless I forced it.But I notice that every time I edit the record that timestamp field gets reset to the current time. Is that the correct way that field is supposed to function? And if so how do I use the timestamp in a field that will not change when the record gets edited?
View Replies !
Simple Install
i want to install mysql-max version from the tar file i downloaded. i wish to use it sql node in cluster. so i followed the instructions in chap 15 mysql ref manual. but could not find specific instructions to install sql node. and how to configure the my.cnf .
View Replies !
Simple Syntax
I have a huge database table design that is all integrated into four areas...very complex. I paid a coder to do the sql set up for me, but now a year later, I need to upgrade from phpBB to vBulletin (of course right)... So I need to quickly find the table columns named user_id to make sure I have them all accounted for.... So I would like to do this SELECT * FROM * WHERE *.user_id > 0 But it doesn't seem to work. The asterisk I guess only works for tables full names, as opposed to ALL tables that have a user_id column... Is there a simpler way to do this than just looking at all 216 tables for user_id.
View Replies !
|
TRACKER
