CODEWITHSUNDEEP
sql
Hanamichi
Hanamichi

Reputation: 1

Update specific column with the value of single column from another table

I want to update the specific column with value of a single column from another table using sql query.

The specific column I want to update is in the WorkingTime table. Like for example I want to update its value by 1 depending on its IDNo and Date from TABLE 2

WorkingTime table:

 IDNo   | PeriodDate | SPLP | NVLP | NBLP | PLP | BLP | MLP | SLP | VLP | NSLP |
18-031  |06/11/2017  |  0   |  0   |  0   |  0  |  0  |  0  |  0  |  0  |  0   |
18-032  |06/12/2017  |  0   |  0   |  0   |  0  |  0  |  0  |  0  |  0  |  0   |
18-033  |06/13/2017  |  0   |  0   |  0   |  0  |  0  |  0  |  0  |  0  |  0   |

And the name of the column I want to update in WorkingTime is in the LeaveDetails. The column with the name LeaveType.

LeaveDetails:

Contro  | IDNo   | LeaveType| DateFrom | DateTo   | NoOfDays |
000041  |18-031  |  SPLP    |06/11/2019|06/11/2019|  1       |
000042  |18-032  |  NVLP    |06/12/2019|06/12/2019|  1       |
000043  |18-033  |  PLP     |06/13/2019|06/13/2019|  1       |

And my expected result after the query is this

Expected result:

 IDNo   | PeriodDate | SPLP | NVLP | NBLP | PLP | BLP | MLP | SLP | VLP | NSLP |
18-031  |06/11/2017  |  1   |  0   |  0   |  0  |  0  |  0  |  0  |  0  |  0   |
18-032  |06/12/2017  |  0   |  1   |  0   |  0  |  0  |  0  |  0  |  0  |  0   |
18-018  |06/13/2017  |  0   |  0   |  0   |  1  |  0  |  0  |  0  |  0  |  0   |

Can anyone please help me with the query i will use to make this happen. Thank you in advance.

Upvotes: 0

Views: 74

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One method is a series of case expressions and subqueries:

update table1
    set splp = (case when exists (select 1
                                  from table2 t2
                                  where t2.idno = table1.idno and
                                        t2.perioddate = t1.perioddate and
                                        t2.leavetype = 'SPLP'
                                 )
                     then 1 else table1.splp
                end),
        splp = (case when exists (select 1
                                  from table2 t2
                                  where t2.idno = table1.idno and
                                        t2.perioddate = t1.perioddate and
                                        t2.leavetype = 'NVLP'
                                 )
                     then 1 else table1.nvlp
                end),
     . . .;

Depending on the database, there are other approach as well, but the above should work in any database.

Upvotes: 0

Ian Ward
Ian Ward

Reputation: 98

I'm unclear which tables/columns you are trying to update but you can update a column value in one table using the column value of a different table by using a sub query, for example:

UPDATE TABLE_1
SET LEAVETYPE = 
(SELECT NEW_COLUMN FROM TABLE_2 WHERE PRIMARY_KEY = 'Something')
WHERE PRIMARY_KEY = 'Something'
;

Upvotes: 1

Nur-a Nusrat Nazmi
Nur-a Nusrat Nazmi

Reputation: 11

Try to Use temp table. first take your data in temp table then update main table by using temp table data. Or using join the table update u set u.assid = s.assid from ud u inner join sale s on u.id = s.udid

Upvotes: 0

Related Questions