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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Nested SQL Statement
I'm new to MySQL and I use this frequently with SQL Server 2000. I get an error and want to know if there is something equivalent with MySQL? Code:
View Replies !
Nested Select Statement
I am working on a feature for my website (MyRoladex.com) where i have a script run through the database once per hour, look to see who has events scheduled that are within the next hour, then look to see what their email/sms/text message preferences are, then email/text them a notification that it is close to time for their scheduled event. Under the Accounts table, i have 5 notable fields: uid, email, mobile, timezone, messagetype Under the DatebookEvents table, the important field is, eventdate (besides 'uid', obviously), which is is YYYY-MM-DD HH-mm-SS format. I did not want to calculate the time zone difference for each user, and then compare it to each of the event dates, and then to the system time, just to see if a notification should be sent, as this would be an immense waste of cpu and bandwidth. The next best thing that i though of was to STORE the event date PLUS or MINUS the time zone difference, whenever the user creates a new event, and then, whenever the user displays their events, it would ADD or SUBTRACT the time zone to the event date, so that it will display correctly for them, AND when the cron job runs once every hour, it will ONLY have to compare the eventdate field with the system time. This is better because the cron job will run once per hour, and have to work with, perhaps, thousands of records, whereas a user may only display his records once or twice per day, thus saving cpu and bandwidth overhead When i was writing the query for the displaying of event details, where i take the event date for the entry they are viewing and ADD or SUBTRACT their time zone to it so that it displays properly according to their time, i came across this problem: Select eid,(eventdate + hour(select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) from DatebookScheduler where uid='666' Returns: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) ' at line 1
View Replies !
Nested Select Statement Error
the mysql that is loaded on the host i am on is mySQL 4.0.24 whereas the one i am using is mySQL 5.0.27 on my computer. i am having some weird problems where nested statements like this are not working on the server i am on... select * from table1 where id = (select id from table2 where user = 1); this sql statement works fine on my computer but not on the server. it says there is a syntax error when it is run on the server. but this sql doesn't look wrong to me, or it does? i have a feeling that sub queries is not implemented in mysql 4.0 and earlier....but i really don't wish to change all the sql in my scripts to the old method where i have to select * from table1, table2 where table1.id = table2.user; because it will be quite troublesome...
View Replies !
SHOW TABLE STATUS & The Nested SELECT Statement
The "SHOW TABLE STATUS" command seems to return a result set, just like "SELECT * FROM SomeTable". Therefore, since the following is valid: SELECT * FROM (SELECT * FROM SomeTable) I inferred that this functionality would extend to the SHOW statement: SELECT * FROM (SHOW TABLE STATUS FROM SomeDataBase) But such a command gives an invalid syntax error. I want to query the data_length from a few tables, place those values into temp variables and then run a second SQL command using those values. Is what I'm trying to do possible?
View Replies !
Nested Select Statement Taking Hours To Execute
I am running a sub select statment so I can compare a couple of tables. If I run the statement from two test tables of 1000 records in each, it will take nine seconds to get the results that I need. However, if I take the same statement and point it to the two tables which contain all the data I need to run against (50560 and 161680 records respectively), it takes hours to run...how long I'm not sure. I fired the statement off yesterday afternoon @ 3pm and it is currently 11:30am the following day...the select statement is still executing. Does anyone have performance on something like this?
View Replies !
Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like: SELECT yada yada yada FROM schedule AS 's1' WHERE ( SELECT skippedstop_id FROM skippedstops WHERE schedule_id=s1.schedule_id AND location_id=$origin ) == "EMPTY SET" Only I have no idea how to actually test for the empty set from the nested select...
View Replies !
Using Update With Nested Select
I've got two tables. wp_gameUsers & wp_achievements. Each have a field called points, and I would like to be able to pull a selected record of points from wp_achievements and add it to a selected tally in wp_gameUsers. Like so; UPDATE wp_gameUsers SET points = points + (select points from wp_achievements where achieve_ID = 8 ) where id = 1 Except whenever i do this it updates the number twice, Eg; 250 points from wp_achievements = 500 points.. The ID is unique I just can't figure out what is going on.
View Replies !
Convert UPDATE Query To INSERT
I have an SQL dump file of a sql database that was generated with the UPDATE export type. The file contains code for generating the various tables inside the database along with the UPDATE lines for the table data. Is there a way to process the dump file such that all of the UPDATE instructions are converted to INSERT instructions? I have to restore the database from scratch so there are no existing table rows to update.
View Replies !
How To Convert SELECT Query (with Left Joins) Into UPDATE ?
SELECT t0.type, t0.datekey, t1.datekey as prev_seen FROM t_detail t0 LEFT JOIN t_detail t1 ON t0.type = t1.type AND t0.count-1 = t1.count order by t0.type, t0.datekey ASC It creates a table with the 'most recent previously seen' date for each date, count is already used to number the 'types'. I used it to create a new_table - OK and added an auto increment id - OK. Now I want to modify the select query to update new_table (which is a complete refresh of its contents, that's ok) how to do with the left join?
View Replies !
Nested Sets And Filtering Conversion To Nested Arrays, How?
I am currently working on a project where we want to reuse a tree structure stored in the database. The tree are basically groups/categories of all the content available within the project. Now we want to reuse this data in forum and only show the featured items/nodes. An idea of the tree structure is: + root + Music (f) + Genre - Electronic (f) - Classical (f) Now the problem is that we don't wan to show the whole tree structure within this new forum. But only the ones for example, which are flagged to be featured. For this, we have a field in the groups table. Only I happen to have some problems with reusing this tree hierarchy: The main problem I have is that when I get the whole tree structure using the query SELECT * groups ORDER BY left_id then I have the whole tree. Only when I add "WHERE featured=1" then the left and right node values are becoming invalid because I filtered out possible child or parent nodes. I am curious if anyone could help me how to get the structure in such way I can easily reuse it. I would love to have the featured nodes in a nested array. Something like: root children: music children: electronic, classical etc.
View Replies !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
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 !
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 !
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 !
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 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 !
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 !
Nested Sql
if nested select statement will work in mysql 3.23.53? i have a simple nested select statement which i believe no error on it. here is my nested sql SELECT picid FROM picture WHERE picid IN (SELECT picid FROM lightbox_image WHERE lbid =1)
View Replies !
Nested IF Statements
Basically i'm asking why doesn't this sql work? One If works fine but i'm not sure if you can nest them like this.. is there a better way? This is narrowing down a products table by a variable being passed that is either subcategory, category or neither and then it narrows down by a brand if a brand is being passed. SELECT * FROM prodSubCat INNER JOIN (prods INNER JOIN prodCat ON prods.prodCatID = prodCat.prodCatID) ON prodSubCat.prodSubCatID = prods.prodSubCatID WHERE (IF(subCatIDParam = 0,(IF(catIDParam = 0, prods.prodCatID LIKE '%',prods.prodCatID = catIDParam)), prods.prodSubCatID = subCatIDParam)) AND (IF(brandIDParam = 0,prods.prodBrandID LIKE '%',prods.prodBrandID = brandIDParam)
View Replies !
Nested Query
I have two tables one is called Targets and the other Leads: Targets Fields: RecordID, TeamName, Manager, FebTGT Leads Fields: RecordID, TeamName, DateLeadSubmitted, NewSale (value of NewSale defaults to 1) Basically what I want to do is to show ALL the teams and their specific targets from the Targets table and then also show if they have made any leads between two dates - this info would come from the Leads table if they have made no leads I still want their info from the first table to be displayed.I have tried using a LEFT JOIN and this works fine until I have to apply the WHERE condition for the date parameters.
View Replies !
Nested SELECTS And SUM()
trying to calculate the amount owed by a customer: SELECT c.compdirectdebit_number, SUM( ( cr.cramount + cr.crvat ) - ( SELECT SUM( cppaid ) AS paid FROM companypaid cp WHERE c.compcompanyid=cp.cpcompanyid) ) AS debit FROM company c, companyreminder cr WHERE c.compcompanyid = cr.crcompanyid GROUP BY c.compcompanyid I want to sum the amount+vat and then - the SUM of the payments which should give us the amount owed. The nested select though is obvioulsy wong, but since this is the second time Ive used them, im struggling with the syntax.
View Replies !
Nested Select Using Like
I am trying to use the results of a nested select statement in the 'parent' statement using 'LIKE' as per: SELECT * FROM t1 WHERE c1 LIKE (SELECT c7 FROM t2 WHERE ID = 987); Does anyone know if this is possible? I am getting empty sets when I experiment with the placement of the % signs. For example: SELECT * FROM t1 WHERE c1 LIKE "%(SELECT c7 FROM t2 WHERE ID = 987)%"; Empty set (0.01 sec) Also I have tried to assign the result of the nested select to a variable like: SELECT @v := c7 FROM t2 WHERE ID = 987; But again then have no idea how to put this variable "@v" into a statement using LIKE. Have tried: SELECT * FROM t1 WHERE c1 LIKE '%@v%'; Empty set (0.00 sec)
View Replies !
|
TRACKER
