Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 10-03-2009
Member
 
Join Date: Nov 2008
Posts: 1,001
Request Update with Select * from the same table

I try to do this work and its not an idea?

Code:
UPDATE machine_280 as e 
SET e.driver = '0' 
WHERE e.ID = ( SELECT MAX(de.ID) FROM machine_280 as de WHERE de.number='290' AND de.date_time<='2009-02-03 04:12:00' AND de.driver='2' AND de.curr<>'0' );
I'm in MySql.
Reply With Quote
  #2  
Old 10-03-2009
Member
 
Join Date: Feb 2008
Posts: 194
Re: Request Update with Select * from the same table

I do not understand "as"

why you do not declare direct "e" and "de"?
Reply With Quote
  #3  
Old 10-03-2009
Member
 
Join Date: Nov 2008
Posts: 1,001
Re: Request Update with Select * from the same table

Its my habit. But even without that it does not work:

You can not specify target table 'e' for update in FROM clause
Reply With Quote
  #4  
Old 10-03-2009
Member
 
Join Date: May 2008
Posts: 945
Re: Request Update with Select * from the same table

That will cause an error like this:

Quote:
#1093 - You can't specify target table 'users' for update in FROM clause
which is normal for mysql

UPDATE Syntax:
Quote:
Currently, you cannot update a table and select from the same table in a subquery.
if your request is described in a literal way, we obtain the following:
give the value "0" the field "driver" of table "machine_280" when the "ID" field is equal to "ID" the greatest of the table "machine_280" among those who have both the value "290" for the "number", the date less than or equal to '2009-02-03 04:12:00', the value "2" to "driver" and "curr" different from "0"

attention for use as the motion is that the MAX() can return a single value. a priori, the ID field is probably the primary key of the table and is therefore to auto increment should not pose de problem

As against, it follows that when updating a single line of your table (only one ID returned in the second part of the motion, so a single ID in the first part)

If the request is made at the beginning really need a ton, the following should be able to replace:

Code:
UPDATE machine_280 SET driver = '0' 
WHERE number='290' AND date_time<='2009-02-03 04:12:00' AND driver='2' AND curr<>'0' 
ORDER BY ID DESC 
LIMIT 1
Reply With Quote
  #5  
Old 10-03-2009
Member
 
Join Date: Nov 2008
Posts: 1,001
Re: Request Update with Select * from the same table

Thank you very much, it works as you said
Reply With Quote
  #6  
Old 13-03-2009
Member
 
Join Date: Mar 2009
Location: Ghana
Posts: 15
Re: Request Update with Select * from the same table

the "de" and the "as" is not accepted by any sql change it and try again
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Request Update with Select * from the same table"
Thread Thread Starter Forum Replies Last Post
Spybot update gives warning "Please select some update files from the list first." Yuksha Networking & Security 4 28-08-2012 06:53 PM
Request for Update Scanner in windows vista Alfiee Hardware Peripherals 6 16-07-2011 10:24 AM
PHP mysql select unique records from one table Captain Carrot Software Development 6 13-05-2010 12:18 PM
Select unique records from a table Conraad Software Development 5 13-05-2010 12:01 AM
How to select multiple table in MySql Doroteo Software Development 4 05-11-2009 09:28 PM


All times are GMT +5.5. The time now is 10:54 PM.