How To Do EXPLAIN For DELETE/UPDATE SQL Statement?
Anyone know how to do EXPLAIN for DELETE/UPDATE SQL Statement?
cause I only can see execution plan from SELECT statement. And anyone know if there is third party software which allow me to see the UI execution plan like MSSQL Query Analyzer?
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Delete From Table Statement Doesn't Delete
I have a weird thing happened in my application where I have run a statemement Delete from tblmysqlReceipt; but later I checked through in my log file there line which have been delete is still exist. If any one can help me I can even post the log files too.
View Replies !
ON DELETE CASCADE ON DELETE UPDATE
I'm using this "reference_definition" with the motor MyISAM and version 5.0.27-community-nt, and it doesn't work. The child tables don't delete (with "ON DELETE CASCADE") or doesn't update (with "ON UPDATE CASCADE")
View Replies !
Delete Statement
Anyway, this is the statement: DELETE t1 FROM rehabs t1, rehabs t2 WHERE t1.name = t2.name AND t1.address1 = t2.address1 AND t1.address2 = t2.address2 AND t1.city = t2.city AND t1.zip = t2.zip AND t1.phone2 IS NULL AND t1.url2 IS NULL AND t1.rehabID <> t2.rehabID I'm using MySQL 5.0.45 The full error message: MySQL said: #1030 - Got error 134 from storage engine If I remove AND t1.phone2 IS NULL AND t1.url2 IS NULL It will work, but I need those fields.
View Replies !
Delete Statement Problem
I am using a simple delete statement in my php script (below) $q = "DELETE FROM commenttype WHERE TypeID=".$_POST['selectcat']; the problem is it doesnt delete the whole row it only deletes the text in one of the fields of the row. Has anyone else had a similar problem? It works when I run the sql directly from a program such as mysqlphp
View Replies !
DELETE Statement Locking
Every night we delete data more than 7 days old to keep the database from getting too big. During this time, no other queries can run, they all end up being locked for like an hour.Is there a way I can configure the DELETE FROM statement where it doesn't affect SELECT queries and possibly the INSERTs as well?
View Replies !
Subquery With Delete Statement
Can we use subquery with Delete statement, when I execute the following Delete statement, I'm getting error msg. DELTE imprs_rep_access FROM imprs_rep_access WHERE access_rep_id IN (SELECT * FROM imprs_rep_access ra WHERE ra.access_url ='/' AND ra.access_rep_id NOT IN (SELECT min(ra.access_rep_id) FROM imprs_Rep_access ra WHERE ra.access_url='/' GROUP BY ra.session_id)) Error Msg: ERROR 1093 (HY000): You can't specify target table 'imprs_rep_access' for update in FROM clause
View Replies !
FORCE INDEX In A DELETE Statement
The following "force index" works fine when in a SELECT statement. However, mysql returns an Invalid Syntax error when it's in a DELETE statement. this works... SELECT message_id FROM anons FORCE INDEX (receiver) WHERE ssave=0 AND rsave=0 AND receiver=1 AND message_id NOT IN (7, 10) this doesn't... DELETE FROM anons FORCE INDEX (receiver) WHERE ssave=0 AND rsave=0 AND receiver=1 AND message_id NOT IN (7, 10) EXPLAIN SELECT tells me I need to force that index. Wouldn't mysql use the same PLAN for DELETE queries with the same conditions? Is it possible to force an index with a DELETE statement?
View Replies !
ASP.net, VB, SQLDataSource, Delete Statement And Parameters
I migrating an SQL Server app to MySQL 5.0 using the .net 5.2.4 connector. I have an SQLDatasource control, this has the following DeleteStatement defined: Delete from customers where customers.id=@ID The deleteParameters for the control looks like this: <DeleteParameters> <asp:Parameter Name="ID" Type="Int32" /> </DeleteParameters> Execution fails with the error Parameter '@ID' must be defined Similar Select, Insert and Update statements work fine. All suggestions as to the correct approach gratefully appreciated. A variation on this statement did not work using the ODBC driver, I appreciate I may have a syntax error which I'm blind to!
View Replies !
Complicate Delete-statement Involving Two Tables
I have two tables: Persons and Relations Persons contain personal data such as person_ID, name, adresse, etc. and a special-status-code (a person with this code set = 1 is a VIP) Relations contains a person_ID and a vip_ID (the vip_ID is in fact a person_ID - it is just a person with his special-status-code = 1). So the relation table is used to register who a VIP knows (his contacts). My problem is when I delete a VIP. I have no problems just deleting the VIP from the Persons-table, but I also need to delete all of his contacts (not only from the Relations-table but also from the Persons-table). I think this might be part of the solution: ALTER PROCEDURE Delete_Vip ( @Vip_ID INT /* ID of the VIP to be deleted */ ) AS DELETE FROM Persons p WHERE r.Vip_ID = @Vip_ID AND p.Person_ID NOT IN ( SELECT * FROM Relations r WHERE Vip_ID <> @Vip_ID ) This does not work, but the idea in the subquery is to NOT delete persons that are known by other VIP's than the one I am deleting. In other words I am only interested in the VIP's exclusive contacts. I am also quite sure that the above SQL-statement does not remove the VIP's posts in the Relations-table.
View Replies !
Update Better Than Delete
running mysql 4.1 on a dedicated server (it's actually a non-standard cluster setup) just wantd to know, i realise that mysql locks tables for updates but does it lock tables for deletes? and if not, would a DELETE followed by an INSERT be faster than one UPDATE statement?
View Replies !
Cascase Update And Delete
I have two tables (both InnoDB) and I want to have one updated on the insert of data in another. The two table names are: team_tbl and team_ostat_tbl. When a new entry into the team_tbl is conducted, I want a record to automatically be created in the team_ostat_tbl. This is what I have: team_tbl: team_id (PK) and some other columns. team_ostat_tbl: team_ostat_id (PK), team_id (FK) and some other columns. I have cascade update on, but when I insert a record into the team_tbl nothing is inserted in the team_ostat_tbl.
View Replies !
Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE. However, when I tried testing that this works by entering:- select * from address, name where address.name_ID=name.ID; It says empty set. What am I doing wrong?
View Replies !
Dry Run Of A Mysql UPDATE/REPLACE/DELETE
Is there anyway to check what an UPDATE etc. query will do to the database before it actually does it? More like a test run of the query. What I would like to do is: 1) See the number of rows affected 2) See what those rows look like currently 3) See what the rows would look like after the query I am trying to write a little php script that syncs two tables from different databases, but would like to be able to run it first without making any changes. Unfortunately I am stuck with mysql 4.0.18.
View Replies !
Triggers To Confirm Update Or Delete
I need to create a trigger that notifies me that a table I have created has been updated or added to. Basiccally I am using a website to send details to my database but I need to know when data has been submitted.
View Replies !
Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE. However, when I tried testing that this works by entering:- select * from address, name where address.name_ID=name.ID; It says empty set. What am I doing wrong?
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 !
Foreign Key :: 1451 - Cannot Delete Or Update A Parent Row
I have a customer table and a products table. The clientID is a foreign key inside the products table. If i edit the clientId inside the product table i get: 1451 - cannot delete or update a parent row; a foreign key constraint fails ('database01/customers', CONSTRAINT 'client_ibfk_1 'FOREIGN KEY('clientId') REFERENCES 'Products' ('clientId')) any ideas ? :| From the customers table : CONSTRAINT `client_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES 'Products`
View Replies !
Linking Tables :: Automatic Update And Delete?
I'm new to MySQL and had a question about "linking" tables. Basically I want two tables that automatically update each other when changed. When something is added in table a I want it to be added in table b, and vice versa. Or when something is changed or deleted in table a I want it to apply to table b aswell, and vice versa again. Is this possible? maybe through the actual tables or a script of some sort. I'm using phpMyAdmin if it helps.
View Replies !
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.
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 !
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 !
Lock Tables To Update And Delete, But Insert New Records
I'm trying to "lock" mysql tables - to prevent rows being updated/deleted - I still want to be able to insert new rows (legal reasons). I have toyed with the idea of changing the user permissions - can I simply change it so that no-one can perform an update on that table, even root? But I feel there must be a simpler way that I and my many web searches am missing.
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 !
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 !
|
TRACKER
