Is it possible to update many rows at a same time?
Following query returns information of current department, an employee is working on.
SELECT a.empID, a.deparmentID
FROM customer a
INNER JOIN (
SELECT f.empID, max(f.myDate) md
FROM customer f
GROUP BY f.empID
) z ON z.empID = a.empID AND z.md = a.myDate
For example,following is the sample of my table:
empID deparmentID myDate
1 1 2011-01-01
2 1 2011-02-10
3 2 2011-02-19
1 2 2011-03-01
2 3 2011-04-01
3 1 2011-05-10
1 3 2011-06-01
So the above query will return,
empID departmentID
1 3
2 3
3 1
Now based on these return values, I want to update my table at one go.
Currently I am updating these values one at a time using for loop(very slow in performance),
my query for updating is :
for row in somerows:
UPDATE facttable SET deparment = row[1] WHERE empID = row[0]
...
but I want to know if it is possible to update all these values at once without using loop.
EDIT:
I have a single table. And I need to query the same table. This table does not have relation to any other tables. The table structure is:
Table Name : Employee
Fields: EmpID varchar
DeptID varchar
myDate date
you can try this
UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END
WHERE id IN (1,2,3)
This is just an example, you can extend it for your case.
Check the manual for more info
Can you try this?
UPDATE customer c
SET depatmentID =
( SELECT a.deparmentID
FROM customer a
INNER JOIN
( SELECT empID
, max(myDate) AS md
FROM customer
GROUP BY empID
) AS z
ON z.empID = a.empID
AND z.md = a.myDate
WHERE a.empID = c.empID
)
or this:
UPDATE customer AS c
SET depatmentID = a.derpmentID
FROM customer a
INNER JOIN
( SELECT empID
, max(myDate) AS md
FROM customer
GROUP BY empID
) AS z
ON z.empID = a.empID
AND z.md = a.myDate
WHERE a.empID = c.empID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With