none
update one table based on another table RRS feed

  • Question

  • Hi, could anybody help me.

    Here two tables

    create table dpost  (
    shop_code nvarchar(20),
    product_id int,
    [dpost2019-01] int) ;

    insert into dpost values

    ('02293НСК',11626,1),
    ('02293НСК',11628,1),
    ('02293НСК',11683,1),
    ('02293НСК',11691,1),
    ('02293НСК',11709,1),
    ('02293НСК',11713,1),
    ('02293НСК',11741,1),
    ('02293НСК',11747,1),
    ('02293НСК',13163,1),
    ('02293НСК',13269,1),
    ('02293НСК',13284,1),
    ('02293НСК',13309,1),
    ('02293НСК',13328,1);


    create table reteff  (
    shop_code nvarchar(20),
    product_id int,
    [perc2019-01] int) ;

    insert into reteff values

    ('02293НСК',11626,0.22),
    ('02293НСК',11628,0.06),
    ('02293НСК',11683,0.07),
    ('02293НСК',11691,0.42),
    ('02293НСК',11709,0.07),
    ('02293НСК',11713,0.00),
    ('02293НСК',11741,0.00),
    ('02293НСК',11747,0.00),
    ('02293НСК',13163,0.50),
    ('02293НСК',13269,0.67),
    ('02293НСК',13284,0.02),
    ('02293НСК',13309,0.00),
    ('02293НСК',13328,0.00),

    how to update dpost table by this way. If group shop_code+product_id in reteff table  by perc column>0.49, then change value for this group in dpost table table from 1 to zero

    I.E. output

    shop_code product_id dpost 2019-01
    02293НСК 11626 1
    02293НСК 11628 1
    02293НСК 11683 1
    02293НСК 11691 1
    02293НСК 11709 1
    02293НСК 11713 1
    02293НСК 11741 1
    02293НСК 11747 1
    02293НСК 13163 0
    02293НСК 13269 0
    02293НСК 13284 1
    02293НСК 13309 1
    02293НСК 13328 1

    cause 13163 and 13269 in reteff table >0,49

    Monday, April 1, 2019 11:23 AM

Answers

  •    update dpost set [dpost2019-01]=0
       from dpost join reteff on dpost.product_id=reteff.product_id
       where reteff.[perc2019-01]>0.49

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 11:45 AM
    Answerer
  • Have you already try something like this code below?

    How is the datatype of column [dpost2019-01] in the reteff table? Because the column contains a decimal value and the column definition is INT.

    update [dpost]
    set [dpost2019-01] = 0
    from [reteff] as b
    where b.[dpost2019-01] > 0.49


    Waiting your reply.


    Felipe Lauffer
    MCT | MCSE: Data Management & Analytics | MCSE: Data Platform | MCSA: SQL Server 2012/2014 | MCP

    [ Blog ] - [ Profile ] - [ Wiki ] - [ Gallery ] - [ LinkedIn ]



    • Edited by FLauffer Monday, April 1, 2019 12:07 PM ADD CODE BOX
    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 11:50 AM
  • sounds like a simple update

    update d
    set [dpost2019-01] = 0
    from dpost  d
    join reteff r
    on r.shop_code = d.shop_code
    and r.product_id = d.product_id
    where r.[perc2019-01] > 0.49

    Ideally I would have expected you to solve this by yourself, with experience you have on the forums

    You've been posting many questions of this level so its high time you start solving these basic questions by yourself and not wait for others to solve it for you


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 12:01 PM
  • create table dpost  (
    shop_code nvarchar(20),
    product_id int,
    [dpost2019-01] int) ;
    
    insert into dpost values
    
    ('02293НСК',11626,1),
    ('02293НСК',11628,1),
    ('02293НСК',11683,1),
    ('02293НСК',11691,1),
    ('02293НСК',11709,1),
    ('02293НСК',11713,1),
    ('02293НСК',11741,1),
    ('02293НСК',11747,1),
    ('02293НСК',13163,1),
    ('02293НСК',13269,1),
    ('02293НСК',13284,1),
    ('02293НСК',13309,1),
    ('02293НСК',13328,1);
    
    
    create table reteff  (
    shop_code nvarchar(20),
    product_id int,
    [perc2019-01] decimal(6,2)) ;
    
    insert into reteff values
    
    ('02293НСК',11626,0.22),
    ('02293НСК',11628,0.06),
    ('02293НСК',11683,0.07),
    ('02293НСК',11691,0.42),
    ('02293НСК',11709,0.07),
    ('02293НСК',11713,0.00),
    ('02293НСК',11741,0.00),
    ('02293НСК',11747,0.00),
    ('02293НСК',13163,0.50),
    ('02293НСК',13269,0.67),
    ('02293НСК',13284,0.02),
    ('02293НСК',13309,0.00),
    ('02293НСК',13328,0.00);
    
    
    Merge dpost   tgt
    Using reteff src
    on src.shop_code = tgt.shop_code
    and src.product_id = tgt.product_id
    When matched  and src.[perc2019-01] > 0.49
    then 
    UPDATE 
    set [dpost2019-01] = 0;
    
    
    Select * from dpost
    
    drop table reteff,dpost

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:23 PM
    Monday, April 1, 2019 1:04 PM
    Moderator

All replies

  •    update dpost set [dpost2019-01]=0
       from dpost join reteff on dpost.product_id=reteff.product_id
       where reteff.[perc2019-01]>0.49

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 11:45 AM
    Answerer
  • Have you already try something like this code below?

    How is the datatype of column [dpost2019-01] in the reteff table? Because the column contains a decimal value and the column definition is INT.

    update [dpost]
    set [dpost2019-01] = 0
    from [reteff] as b
    where b.[dpost2019-01] > 0.49


    Waiting your reply.


    Felipe Lauffer
    MCT | MCSE: Data Management & Analytics | MCSE: Data Platform | MCSA: SQL Server 2012/2014 | MCP

    [ Blog ] - [ Profile ] - [ Wiki ] - [ Gallery ] - [ LinkedIn ]



    • Edited by FLauffer Monday, April 1, 2019 12:07 PM ADD CODE BOX
    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 11:50 AM
  • sounds like a simple update

    update d
    set [dpost2019-01] = 0
    from dpost  d
    join reteff r
    on r.shop_code = d.shop_code
    and r.product_id = d.product_id
    where r.[perc2019-01] > 0.49

    Ideally I would have expected you to solve this by yourself, with experience you have on the forums

    You've been posting many questions of this level so its high time you start solving these basic questions by yourself and not wait for others to solve it for you


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:04 PM
    Monday, April 1, 2019 12:01 PM
  • Hi Have you ran your script before posting?

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'dpost2019-01'.

    Moreover, it updates all the data in dpost table to 0


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 1, 2019 12:02 PM
    Answerer
  • Hi Have you ran your script before posting?

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'dpost2019-01'.

    Moreover, it updates all the data in dpost table to 0


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    it is ok, your script work and scripts of another members of the forum work too


    Monday, April 1, 2019 1:03 PM
  • create table dpost  (
    shop_code nvarchar(20),
    product_id int,
    [dpost2019-01] int) ;
    
    insert into dpost values
    
    ('02293НСК',11626,1),
    ('02293НСК',11628,1),
    ('02293НСК',11683,1),
    ('02293НСК',11691,1),
    ('02293НСК',11709,1),
    ('02293НСК',11713,1),
    ('02293НСК',11741,1),
    ('02293НСК',11747,1),
    ('02293НСК',13163,1),
    ('02293НСК',13269,1),
    ('02293НСК',13284,1),
    ('02293НСК',13309,1),
    ('02293НСК',13328,1);
    
    
    create table reteff  (
    shop_code nvarchar(20),
    product_id int,
    [perc2019-01] decimal(6,2)) ;
    
    insert into reteff values
    
    ('02293НСК',11626,0.22),
    ('02293НСК',11628,0.06),
    ('02293НСК',11683,0.07),
    ('02293НСК',11691,0.42),
    ('02293НСК',11709,0.07),
    ('02293НСК',11713,0.00),
    ('02293НСК',11741,0.00),
    ('02293НСК',11747,0.00),
    ('02293НСК',13163,0.50),
    ('02293НСК',13269,0.67),
    ('02293НСК',13284,0.02),
    ('02293НСК',13309,0.00),
    ('02293НСК',13328,0.00);
    
    
    Merge dpost   tgt
    Using reteff src
    on src.shop_code = tgt.shop_code
    and src.product_id = tgt.product_id
    When matched  and src.[perc2019-01] > 0.49
    then 
    UPDATE 
    set [dpost2019-01] = 0;
    
    
    Select * from dpost
    
    drop table reteff,dpost

    • Marked as answer by merzavazeh Monday, April 1, 2019 1:23 PM
    Monday, April 1, 2019 1:04 PM
    Moderator
  • Hi merzavazeh,

    Hope the below one will help for you.

    create table #dpost  
    (
    shop_code nvarchar(20),
    product_id int,
    [dpost2019-01] int
    ) ;
    
    insert into #dpost values
    ('02293НСК',11626,1),
    ('02293НСК',11628,1),
    ('02293НСК',11683,1),
    ('02293НСК',11691,1),
    ('02293НСК',11709,1),
    ('02293НСК',11713,1),
    ('02293НСК',11741,1),
    ('02293НСК',11747,1),
    ('02293НСК',13163,1),
    ('02293НСК',13269,1),
    ('02293НСК',13284,1),
    ('02293НСК',13309,1),
    ('02293НСК',13328,1);
    
    
    create table #reteff  
    (
    shop_code nvarchar(20),
    product_id int,
    [perc2019-01] decimal(6,2)
    ) ;
    
    insert into #reteff values
    ('02293НСК',11626,0.22),
    ('02293НСК',11628,0.06),
    ('02293НСК',11683,0.07),
    ('02293НСК',11691,0.42),
    ('02293НСК',11709,0.07),
    ('02293НСК',11713,0.00),
    ('02293НСК',11741,0.00),
    ('02293НСК',11747,0.00),
    ('02293НСК',13163,0.50),
    ('02293НСК',13269,0.67),
    ('02293НСК',13284,0.02),
    ('02293НСК',13309,0.00),
    ('02293НСК',13328,0.00);
    
    update d
    set 
    d.[dpost2019-01] = 0
    from #dpost d inner join #reteff r on r.shop_code = d.shop_code and r.product_id = d.product_id
    where r.[perc2019-01] > 0.49;
    
    select shop_code,product_id,[dpost2019-01] from #dpost;
    
    drop table #dpost;
    drop table #reteff;
    
    

    Note: Suggested one of the many ways that is easy to understand and implement.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Tuesday, April 2, 2019 6:32 PM