Update SQL Server table with join option

Question

As you see the 2 tables, I want to update/sync the data from table Invoice to table HR.

The scenario is to manually input the data in columns HR & PO Number, then update with table Invoice. I think the idea is to join only data with exist PO Number in table HR, no need to get rows if the PO Number does not exist. Also the format of column month looks like a number, so how to get just month in SQL and does not matter the format of date.

In HR table you don't have year column. So, you maintain only one year of data? What happens in the next year? Data for each month is overridden? — fiveelements, Jul 17 '19 at 17:29
@fiveelements, the year not a problem here, I need to update with the month — Med Amin, Jul 17 '19 at 17:32
You want to create an UPDATE query with JOINS? If that is your question, why not search for the answer online? I just did, and I found this: https://stackoverflow.com/questions/982919/sql-update-query-using-joins — MJH, Jul 17 '19 at 17:47
@MedAmin The year is a problem if you want this to work for more than a few more months. — Error_2646, Jul 17 '19 at 17:58
@Error_2646, yes sure, maybe when I select the data with year, but with update just to sync the data with months and the year will be the current year — Med Amin, Jul 19 '19 at 14:47

score 1 · Accepted Answer · answered Jul 17 '19 at 18:04

If year truly isn't a problem, then you can do it statically.

But I'd guess you really want to get a year field added to that HR table.

UPDATE INVOICE
  FROM HR
 WHERE INVOICE.po_number = HR.po_number
   SET INVOICE.amount = 
           CASE WHEN INVOICE.MONTH = 20190124 THEN HR.JANUARY
                WHEN INVOICE.MONTH = 20190224 THEN HR.FEBUARY
                WHEN INVOICE.MONTH = 20190324 THEN HR.MARCH
                WHEN INVOICE.MONTH = 20190424 THEN HR.APRIL
                WHEN INVOICE.MONTH = 20190524 THEN HR.MAY
                WHEN INVOICE.MONTH = 20190624 THEN HR.JUNE
                WHEN INVOICE.MONTH = 20190724 THEN HR.JULY
                WHEN INVOICE.MONTH = 20190824 THEN HR.AUGUST
                WHEN INVOICE.MONTH = 20190924 THEN HR.SEPTEMBER
                WHEN INVOICE.MONTH = 20191024 THEN HR.OCTOBER
                WHEN INVOICE.MONTH = 20191124 THEN HR.NOVEMBER
                WHEN INVOICE.MONTH = 20191224 THEN HR.DECEMBER
                ELSE INVOICE.AMOUNT
            END;

This query updates amount in Invoice table based on the HR table. This is relatively easier. However, the question was the opposite and that is a bit different: "I want to update/sync the data *from table Invoice to table HR*". Then how this is accepted as an answer? — fiveelements, Jul 19 '19 at 14:49
@fiveelements in my question I use an example, and the answer given by Error_2646 gave me an idea how to do it using "Case When" so I accept his answer — Med Amin, Jul 19 '19 at 15:16

score 1 · Answer 2 · answered Jul 17 '19 at 18:55

1

Use pivot to convert Invoice rows into columns (PO number and months). Then update the HR table by joining with the converted table data.

answered Jul 17 '19 at 18:55

fiveelements

3,649
1
17
16

Update SQL Server table with join option

2 Answers2