CODEWITHSUNDEEP
sqlpostgresql
Rohita Khatiwada
Rohita Khatiwada

Reputation: 2935

Updating multiple rows at once

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

Upvotes: 4

Views: 25186

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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

Upvotes: 2

Balanivash
Balanivash

Reputation: 6867

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

Upvotes: 12

Ovais Khatri
Ovais Khatri

Reputation: 3211

This could only be possible if you want to update each with same value, then you could use where clause with IN.

Upvotes: -2

Related Questions