Update date from other table-CodePudding

I have a table called Products_V which has the following columns.

ProductNumber
Source
date

I want to update date of the table called Product_feed which has the following columns.

ProductNumber
source
date

I want to update the date of table Product_feed by taking the date from table Product_V I want to join the tables using columns ProductNumber and Source but the problem is values of source is different in both the tables for same ProductNumber.

Like in Table Product_V I have value of Source as SOD

and in Table Product_feed I have value of Source as 'MBUS_MOD' for the same ProductNumber.

The update statement I used:

set T1.date =T2.date
from Product_feed T1
join (
    select
      date,
      ProductNumber,
      Source = IIF('MBUS_MOD' ,'SOD' ,NULL)
    from Product_V
) T2
  on T1.Source = T2.Source
  and T1.ProductNumber = T2.ProductNumber

CodePudding user response：

If you have unique ProductNumber and if it's not depending on Source (multiple source value for same ProductNumber and in same table) then you don't have to add Source in join statement and below query will work for your scenario

update
    Product_feed
set
    date = Products_V.date
from
    Products_V
where
    Product_feed.ProductNumber = Products_V.ProductNumber;

CodePudding user response：

First of all, you must avoid special words used for the SQL like 'date','Source' etc.

here is a sample code for your Problem, hope it will help.

Use master
Go

Declare @ProductsV As Table(
    ProductNumber NVarchar(50) Not Null Default '',
    SourceID NVarchar(50) Not Null Default '',
    VDate date Not Null Default ''
);

Insert Into @ProductsV(ProductNumber,SourceID,VDate)
Select ProductNumber,SourceID,VDate
From(
    Select ProductNumber='MUL-123-0981',SourceID='SOD',VDate='12/15/2021'
    Union All
    Select ProductNumber='MUL-124-0982',SourceID='SOD',VDate='12/16/2021'
    Union All
    Select ProductNumber='MUL-125-0983',SourceID='SOD',VDate='12/17/2021'
    Union All
    Select ProductNumber='MUL-126-0984',SourceID='SOD',VDate='12/18/2021'
    Union All
    Select ProductNumber='MUL-127-0985',SourceID='SOD',VDate='12/19/2021'
    Union All
    Select ProductNumber='MUL-128-0986',SourceID='SOD',VDate='12/20/2021'
) As VData;

Declare @ProductFeed As Table(
    ProductNumber NVarchar(50) Not Null Default '',
    SourceID NVarchar(50) Not Null Default '',
    FDate date Not Null Default ''
);

Insert Into @ProductFeed (ProductNumber,SourceID,FDate)
Select ProductNumber,SourceID,FDate
From(
    Select ProductNumber='MUL-123-0981',SourceID='MBUS_MOD',FDate='11/15/2021'
    Union All
    Select ProductNumber='MUL-124-0982',SourceID='MBUS_MOD',FDate='11/16/2021'
    Union All
    Select ProductNumber='MUL-125-0983',SourceID='MBUS_MOD',FDate='11/17/2021'
    Union All
    Select ProductNumber='MUL-126-0984',SourceID='MBUS_MOD',FDate='11/18/2021'
    Union All
    Select ProductNumber='MUL-127-0985',SourceID='MBUS_MOD',FDate='11/19/2021'
    Union All
    Select ProductNumber='MUL-128-0986',SourceID='MBUS_MOD',FDate='11/20/2021'
) As FData;

Select * From @ProductsV;
Select * From @ProductFeed ;

Update FData Set FData.FDate=VData.VDate
From @ProductsV As VData Join @ProductFeed As FData On (FData.ProductNumber=VData.ProductNumber) And (FData.SourceID='MBUS_MOD') And (VData.SourceID='SOD') And (FData.FDate<>VData.VDate);

Select * From @ProductsV;
Select * From @ProductFeed;