Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with an Update based on Max()

Posted on 2009-04-23
7
Medium Priority
?
260 Views
Last Modified: 2012-05-06
I have a contract table and I would like to make an update to the contract which has the max contract value:

UPDATE contract
set status= 'PRIORITY
WHERE ContractID = (SELECT c.ContractID FROM Contract c ORDER BY c.Value DESC Limit 1)

I am using MYSQL which wouldn't allow me to use First() hence the LIMIT 1

I was originally planning to use MAX() but couldn't get that to work either. Any help would be great.

0
Comment
Question by:directxBOB
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24220787
This is how you would do it with MS SQL Server, perhaps it is similar in MySQL:
UPDATE contract
set status= 'PRIORITY'
WHERE ContractID In (SELECT TOP 1 ContractID FROM Contract ORDER BY Value DESC)
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24220807
Try this :

update contract
set status= 'PRIORITY' 
where ContractID =  (select MAX(ContractID) from contract)

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24220922
But that is not the same as what they want (even when using MySQL).  All you have done is update the largest value of ContractID with 'PRIORITY', when they want to only update the ContractID that has the highest "Value"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24220942
sorry mistaken (over looked :-))
0
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24220972
As acperkins says

UPDATE [Contract]
SET [Status]= 'PRIORITY'
WHERE ContractID In (SELECT TOP 1 ContractID FROM [Contract] ORDER BY Value DESC)
[OR]

UPDATE [Contract]
SET [Status]= 'PRIORITY'
WHERE ContractID = (SELECT TOP 1 ContractID FROM [Contract] ORDER BY Value DESC)
should work fine.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24221059

In MySQL, you cannot use the same table name in the FROM clause. To do this in MySQL, try like this.
My question here is, does the c.value unique in your table?
If yes this is fine.
UPDATE contract set status= 'PRIORITY' ORDER BY c.Value DESC Limit 1 

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24221601

This query is even more better and will take care even if you have more than one record matching the creteria.
update contract c1,(select contractId from contract where Value in (select max(Value) from contract)) c2
   set c1.status = "Priority"
 where c1.contractId = c2.contractId;
Hope the attached xamples helps you in understanding. I hope you can modify the query to your needs with the help of my examples if my understanding of your requirement is wrong.


Example 1:
 
In this example we have max(Value) as "z" and the corresponding ContractId as 2. There are two records with contarctId as 2.
I am updating those two records now.
 
mysql> select * from contract;
+------------+--------+-------+
| contractid | status | Value |
+------------+--------+-------+
|          1 | abc1   | x     |
|          2 | abc2   | y     |
|          2 | abc3   | z     |
|          4 | abc4   | a     |
|          4 | abc5   | b     |
|          4 | abc6   | c     |
+------------+--------+-------+
6 rows in set (0.00 sec)
 
mysql> update contract c1,(select contractId from contract where Value in (select max(Value) from contract)) c2
    ->    set c1.status = "updated"
    ->  where c1.contractId = c2.contractId;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
 
mysql> select * from contract;
+------------+---------+-------+
| contractid | status  | Value |
+------------+---------+-------+
|          1 | abc1    | x     |
|          2 | updated | y     |
|          2 | updated | z     |
|          4 | abc4    | a     |
|          4 | abc5    | b     |
|          4 | abc6    | c     |
+------------+---------+-------+
6 rows in set (0.00 sec)
 
Example 2:
 
If we have data like below (two records with max(Value) i.e."z") the corresponding contractid is 2 and 4. 
There are 5 records (two records with contract id as 2 and three records with contract id as 4) need to be updated.
 
mysql> select * from contract;
+------------+--------+-------+
| contractid | status | Value |
+------------+--------+-------+
|          1 | abc1   | x     |
|          2 | abc2   | y     |
|          2 | abc3   | z     |
|          4 | abc4   | a     |
|          4 | abc5   | z     |
|          4 | abc6   | c     |
+------------+--------+-------+
6 rows in set (0.00 sec)
 
mysql> update contract c1,(select contractId from contract where Value in (selec
t max(Value) from contract)) c2
    ->    set c1.status = "updated"
    ->  where c1.contractId = c2.contractId;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0
 
mysql> select * from contract;
+------------+---------+-------+
| contractid | status  | Value |
+------------+---------+-------+
|          1 | abc1    | x     |
|          2 | updated | y     |
|          2 | updated | z     |
|          4 | updated | a     |
|          4 | updated | z     |
|          4 | updated | c     |
+------------+---------+-------+
6 rows in set (0.00 sec)

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question