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 Complete Forum Thread with Replies
Related Forum Messages:
Command To Change Case
In ms word, we can change the font to "Title Case". My Question is, What is the mysql command to do to this? (i see there is lcase() ucase() but not this this type)
View Replies !
Case Statement
How do you nest in a case statement? example: CASE WHEN (eli_pipcal_analysis.cv_vs_precision = 'PASSED' and eli_pipcal_analysis.cv_vs_precision = 'PASSED') THEN 'DBLPASS' WHEN (eli_pipcal_analysis.f_status = 'PASSED' and eli_pipcal_analysis.cv_vs_precision = 'FAILED') THEN 'SAVED' WHEN (eli_pipcal_analysis.f_status = 'PASSED' and eli_pipcal_analysis.e_vs_accuracy = 'FAILED') THEN 'SAVED' ELSE 'DBLFAIL' END AS AF_vs_AL
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 !
CASE Statement And NULL
I'm having trouble seeing what's wrong with the following case statement: CASE WHEN @Status='Active' THEN '1' WHEN @Status='Deleted' THEN '-1' ELSE '0' END @status = 'Active' is true and I've put in print statements to verify this, however the Status is always being set to 0. For some reason the case statement isn't recognizing the Status correctly so it thinks that @status is null
View Replies !
Case Statement: Where Value In Range
I've got a table with around 40000 records. There's a column called v_pop with a range of 2 - 1000. I'd like to color code some HTML but use the query to do that work. So check out the code below. It runs just fine, with no errors, BUT, it only returns white and pink as the class, even though I know for a fact that there are records with higher values. What am I doing wrong here? I'm running mySQL 4.1.17 PHP SELECT v_player, CASE WHEN v_pop < 49 THEN 'white' WHEN v_pop > 50 < 99 THEN 'pink' WHEN v_pop > 100 < 199 THEN 'salmon' WHEN v_pop > 200 < 299 THEN 'yellow' WHEN v_pop > 300 < 299 THEN 'orange' WHEN v_pop > 400 < 399 THEN 'red' WHEN v_pop > 500 < 499 THEN 'brick' END AS class FROM x_world
View Replies !
Case Statement And Multiple Conditions
I have a case statement where I have multiple conditions that are translating into one variable that I am using to group my results by. My problem is that I have data that meets more than one of the conditions and I need an accurate count if the data crosses multiple categories. Here is an example of what I have: case when week(smbt.date_submitted) >=35 then 'New' when sc.Status_Code = 80 and rc.Resolution not in (10,30) then 'Resolved' when sc.Status_Code = 90 and rc.Resolution not in (10,30) then 'Closed' when rc.Resolution in (10,30) and sc.Status_Code not in (80,90) then 'Open' else 'All Other' end as status The data that I am pulling can be New and Closed, or New & Resolved or New and Open, or New and Other. Is there anyway to formulate this statement to account for that combination, but still translate into the same "status" variable while keeping each cateogry (New, Resolved, Closed, Open, Other).
View Replies !
CASE Statement Checking Null
I cannot figure out how to get a CASE statement to check a NULL field. I have: SELECT CASE transaction_id WHEN (transaction_id IS NULL) THEN 'out' ELSE 'in' END AS current_status FROM users_current_transaction WHERE user_id = 1 It is always returning 'in' even though the transaction_id for user_id 1 is NULL.
View Replies !
Problem W/ LIMIT In CASE Statement
I'm having a problem w/ the LIMIT clause when I'm using this CASE statement. This is working like a dream: SELECT * FROM table ORDER BY CASE table_id WHEN 54 THEN 0 ELSE 1 END, table_id LIMIT 50 Only problem is I would like to do a limit start, stop statement at the end rather than a limit stop statement. How would you do this: SELECT * FROM table ORDER BY CASE table_id WHEN 54 THEN 0 ELSE 1 END, table_id LIMIT 50, 50 Once I add the second part of the limit, I lose the idea of displaying table_id=54 first. Unfortunately, UNIONS are out of the question because my Mysql version is too old.
View Replies !
If Case Select Statement Problem!!!
I have the following tables: employee (empid,firstname,departmentid, applied_system) department(depid,depname) employeeweek(empid,weekshiftid) weekshifts(weekshiftid, sunday_shiftid,monday_shiftid,tuesday_shiftid,wednesday_shiftid,thursday_shift id,friday_shiftid,saturday_shiftid) dayshift(dayshiftid, from1,to1) Each of the days in the weekshift for example sunday_shiftid is a foreign key for dayshiftid in table dayshift. weekshiftid in table employeeweek is a foreign key from table weekshifts. empid in the same table is a foreign key from weekshifts. departmentid in employee table is a fk from table department! I am willing to query the database to return me the value of the scheduled shift of the employee (from1, to1 fields) in a certain date interval (that is between date1 and date2) and if the empoyee applied system is 1 . First I have to see if the dayname of the date is sunday for example then return the shift that corresponds for that employee in that day... I can't just do it... I Tried mysql documentation but didn't know how i can use the if and case statement properly
View Replies !
SQL Statement - Changing Case Of A Letter Automatically
I am running MySQL on Windows and I have a simple database setup with a list of items (contained in the same column), to keep it simple - let's say it is a list of colors. red white blue I want to be able to run a sql statement to change only the first letter in each word to uppercase so it will read: Red White Blue I think there is a command (but I am not familiar with it) that is suppose to change the entire case of the database table columns for example with : UPDATE Products SET ProductName = UPPER(ProductName) * A WHERE clause could also be used if only certain rows needed to be changed. But I only want to change the first letter as mentioned - does anyone know how to go about this ?
View Replies !
Use Of The 'IN' Command In SQL Statement
I can't for the life of me figure out why this use of the 'IN' command does not work with my SQL. I have tried the SQL statment within the In command and it works fine but when i use it in the below SQL statement it does not work, and doesn't really tell me what is wrong.
View Replies !
UPDATE Large Number Of Rows With CASE Is Very Slow
UPDATE table SET rating = (CASE uniq WHEN 'ACD' THEN 0.6857 WHEN 'GGYB' THEN 0.5831 WHEN 'QUCG' THEN 0.5831 WHEN 'CYP' THEN 0.5831 ... ELSE 0 END), score = (SELECT @t := 1/(1+rating))*votes_up - (1-@t)*votes_down; The table has the column `uniq` as PRIMARY KEY of type VARCHAR(10), and about 500 rows. I need to update the entire table with this query. The problem is the query runs extremely slowly, taking 4.6529 seconds to run. I realize the index is not the most efficient, but is it the main culprit?
View Replies !
Using >= In Select Statement From Windows Command Line?
I've learned from a post by Guelphdad that I can output the results of a SELECT query into a text file (keeping the column headings) using the following (on Windows XP using MySQL). echo select sum(sales) from sales where year(ship_date) = '2008' | mysql -uuser -ppassword mydatabase > outputfile.txt That works just fine. But I run into trouble with: echo select sum(sales) from sales where ship_date >= '2008-01-01' and ship_date <= '2008-05-31' | mysql -uuser -ppassword mydatabase > outputfile.txt An output file is created, but it is zero kB and does not contain any information (as one would expect from 0 kb). I assume that the problem is with my usage of > in ">=" (and possibly also the < of the "<="). I've googled for help escaping > on the Windows command line, but I haven't found anything to solve my problem. I've tried 1. echo select sum(sales) from sales where ship_date ">"= .... (One suggested method for escaping the ">" symbol is to put it in quotes.) 2. echo "select .... " | ... 3. echo ("select ..... " ) | ..... All three atmysqlts gave me SQL errors. 4. echo select sum(sales) from sales where "ship_date >= '2008-01-01'" .... doesn't give me a SQL error, but it gives me the 0 kB file. I know that escaping ">" is really a "Windows" issue, not a SQL issue, but here it concerns a specific use of Windows, namely to put the results of a MySQL query into a text file. So I hoped someone here had already figured out how to do so. I realize that I can avoid the issue with "where ship_date between start_date and end_date". But I am hoping that one doesn't have to give up on using ">=" altogether, as that response would suggest. So is there a way to safely use ">" and "<" in queries executed from the command line?
View Replies !
UPDATE Command
I am having bother with a command to update a record from with in a Visual Studio 2005 GridView control. If anyone can help it'd be great! I have an ASP.Net page where the user can edit records from the database, via a GridView. I have managed to connect to the database table (somehow!) using a SQLDatasource control, which works perfectly, but when you go to update a record, you type the changes, click 'Update' and they havn't made it across. The SQL I use is - UPDATE tblRecord SET Announcement=@Announcement, Expiry=@Expiry, Page=@Page WHERE tblRecordID = @tblRecordID When run in a web browser, the code doesn't return an error, just returns back to the page with un-edited records.
View Replies !
Update Command Without Where Clause
Can anyone help me to recover or undo this problem? I did inadvertently run a command, 'update ..set' without where clause and changed every records. Unfortunately our backup didn't run last night. Is there any way to recover or undo this?
View Replies !
Error On UPDATE Command
on this statemet: UPDATE semaphoreTable SET semaphore = CONTROL_NONE i get this error: Unknown column 'CONTROL_NONE' in 'field list'" it is indeed true that there is no column named CONTROL_NONE. i am trying to set the data in semaphore to CONTROL_NONE semaphoreTable consists of one field, called semaphore, a varchar(50)
View Replies !
MySQL ''UPDATE'' Command...
I am new to fairly new to MySQL and I would like to have some help... I use MySQL database to store all of my user accounts and logins. I need to update all of the data in there automatically without having to do all 10,000 entries manually. My account database has the following tables:
View Replies !
Command To Update Prices
I have one table with the name dummy. In that table there is two columns I want to use, test1 which contains product ID's and test4 which contains prices. Then I have another table with the name products. In that table there amonh other columns two columns, one column with the name products_id with the products ids and another with the name products_prices which is the column I need to update with the prices from the test4 column in the dummy database. So I want to select the prices and products ids from the dummy table and update the prices in the products table. The products ids are the same in both tables and so is the number of lines. The only thing that differs is the prices, in the column test4 in the table dummy the real prices are and in the products table in the products_prices colum the wrong prices are that I want to update. The reason for wanting to to that is that all prices in the table my webshop uses has changed to zero... And that is not good, I can't give away all products.
View Replies !
Using Limit With Select Or Update Command
How come I can do: select field1 from database limit 10 *shows first 10 records like it should select field1 from database limit 1,5 *It returns first 5 rows correctlly update database set field1='first set" limit 10 *It correctlly updates the field for the first 10 records update database set field1="top 5" limit 1,5 *I get an error that says problem with limit at ^5 check command for correct syntax Is there different syntax for limit when using with a select statement Vs. an update command?
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 !
|
TRACKER
