locked
Insert update statement in Sql Query RRS feed

  • Question

  • Hi,

    I have written this query in SQL to get Organization and Product:

                             

    SELECT        dbo.Product.ProductDescription, dbo.OrganisationProductMapping.OrganisationID AS OrganisationId, dbo.Organisation.ShortDescription AS OrganisationName, 
    CONCAT (dbo.OrganisationPostalAddress.Street1, dbo.OrganisationPostalAddress.Street2, dbo.OrganisationPostalAddress.City, dbo.OrganisationPostalAddress.State, 
                             dbo.OrganisationPostalAddress.Postcode, dbo.OrganisationPostalAddress.Country) As Address

    FROM            dbo.Organisation INNER JOIN
                             dbo.OrganisationProductMapping ON dbo.Organisation.OrganisationID = dbo.OrganisationProductMapping.OrganisationID INNER JOIN
                             dbo.Product ON dbo.OrganisationProductMapping.ProductID = dbo.Product.ProductID LEFT OUTER JOIN
                             dbo.OrganisationPostalAddress ON dbo.Organisation.OrganisationID = dbo.OrganisationPostalAddress.OrganisationId


    Where dbo.OrganisationProductMapping.ProductID  in ( '3E2D9BED-78CD-41D2-9A1B-66F495D14C60' , 'E11BF554-5F35-4F2E-B0FA-B5D03C0D18DF', 'A9258ACC-42FB-49C4-9670-683100CE742A', '84AF7199-6A2E-4DFB-8C76-E4041F3046DE')

    Now,after getting the results, there are few rows in address column is empty(without any addresses). I want to update those with empty rows by inserting Street1= Hong Kong, Street2 = HongKong, Country=Hong Kong. How do I do the updating query in this sql query?


    Durga



    Friday, June 17, 2016 8:27 AM

Answers

  • Try this,

    UPDATE dbo.OrganisationPostalAddress
    SET Street1= 'Hong Kong', Stree2='HongKong', Country='Hong Kong'
    WHERE OrganisationId IN (
     SELECT dbo.OrganisationProductMapping.OrganisationID AS OrganisationId
     FROM  dbo.Organisation
     INNER JOIN dbo.OrganisationProductMapping  ON dbo.Organisation.OrganisationID = dbo.OrganisationProductMapping.OrganisationID
     INNER JOIN dbo.Product ON dbo.OrganisationProductMapping.ProductID = dbo.Product.ProductID
     LEFT OUTER JOIN dbo.OrganisationPostalAddress ON dbo.Organisation.OrganisationID = dbo.OrganisationPostalAddress.OrganisationId
     Where dbo.OrganisationProductMapping.ProductID  in ( '3E2D9BED-78CD-41D2-9A1B-66F495D14C60' ,
     'E11BF554-5F35-4F2E-B0FA-B5D03C0D18DF', 'A9258ACC-42FB-49C4-9670-683100CE742A', '84AF7199-6A2E-4DFB-8C76-E4041F3046DE')
    )
    AND (Street1 = '' OR Street2 = '' OR Country = '' OR Street1 IS NULL OR Street2 IS NULL OR Country IS NULL)


    Regards, RSingh


    Friday, June 17, 2016 11:16 AM
    Answerer
  • Try something like below. Make sure that the second query i.e alias Y as unique/latest street or country information for unique Organization ID.

    UPDATE dbo.OrganisationPostalAddress SET Street1= Y.Street1 , Stree2=Y.Street2 , Country= Y.Country FROM ( SELECT dbo.OrganisationProductMapping.OrganisationID AS OrganisationId FROM dbo.Organisation INNER JOIN dbo.OrganisationProductMapping ON dbo.Organisation.OrganisationID = dbo.OrganisationProductMapping.OrganisationID INNER JOIN dbo.Product ON dbo.OrganisationProductMapping.ProductID = dbo.Product.ProductID LEFT OUTER JOIN dbo.OrganisationPostalAddress ON dbo.Organisation.OrganisationID = dbo.OrganisationPostalAddress.OrganisationId Where dbo.OrganisationProductMapping.ProductID in ( '3E2D9BED-78CD-41D2-9A1B-66F495D14C60' , 'E11BF554-5F35-4F2E-B0FA-B5D03C0D18DF', 'A9258ACC-42FB-49C4-9670-683100CE742A', '84AF7199-6A2E-4DFB-8C76-E4041F3046DE') ) X INNER JOIN ( SELECT o.OrganisationId, o.Street1, o.Street2, o.City, o.State, o.Postcode, o.Telephone, o.Country, c.DisplayName, o.Email, o.Fax FROM OrganisationPostalAddress o LEFT JOIN Countries c ON o.Country = c.ISO2 ) Y ON X.OrganisationId = Y.OrganisationId

    WHERE Street1 = '' OR Street2 = '' OR Country = '' OR Street1 IS NULL OR Street2 IS NULL OR Country IS NULL



    Regards, RSingh





    Monday, June 20, 2016 11:10 AM
    Answerer

All replies

  • Hi,

    Below links will help you understand and write set level update statement. Hope it helps.

    http://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement/

    http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql

    Regards,

    Pankaj Rathod

    Friday, June 17, 2016 10:09 AM
  • Try this,

    UPDATE dbo.OrganisationPostalAddress
    SET Street1= 'Hong Kong', Stree2='HongKong', Country='Hong Kong'
    WHERE OrganisationId IN (
     SELECT dbo.OrganisationProductMapping.OrganisationID AS OrganisationId
     FROM  dbo.Organisation
     INNER JOIN dbo.OrganisationProductMapping  ON dbo.Organisation.OrganisationID = dbo.OrganisationProductMapping.OrganisationID
     INNER JOIN dbo.Product ON dbo.OrganisationProductMapping.ProductID = dbo.Product.ProductID
     LEFT OUTER JOIN dbo.OrganisationPostalAddress ON dbo.Organisation.OrganisationID = dbo.OrganisationPostalAddress.OrganisationId
     Where dbo.OrganisationProductMapping.ProductID  in ( '3E2D9BED-78CD-41D2-9A1B-66F495D14C60' ,
     'E11BF554-5F35-4F2E-B0FA-B5D03C0D18DF', 'A9258ACC-42FB-49C4-9670-683100CE742A', '84AF7199-6A2E-4DFB-8C76-E4041F3046DE')
    )
    AND (Street1 = '' OR Street2 = '' OR Country = '' OR Street1 IS NULL OR Street2 IS NULL OR Country IS NULL)


    Regards, RSingh


    Friday, June 17, 2016 11:16 AM
    Answerer
  • If you need just display  the data  you can use CASE expression to address the issue 

    case when dbo.OrganisationPostalAddress.Street1 ='' or dbo.OrganisationPostalAddress.Street1 is null then 'Hong Kong'  else dbo.OrganisationPostalAddress.Street1 end 


    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

    Sunday, June 19, 2016 6:45 AM
  • Hi,

    Thanks for the above code. It will surely work (Thanks for that). But I just found out another issue, that I can't simply set the country = 'Hong Kong' because the country is taken based on a store proc: How to set this into the query you have written above?

    SELECT o.OrganisationId, 
    o.Street1, 
    o.Street2, 
    o.City, 
    o.State, 
    o.Postcode, 
    o.Telephone, 
    o.Country, 
    c.DisplayName,
    o.Email,
    o.Fax
    FROM OrganisationPostalAddress o
    LEFT JOIN Countries c
    ON o.Country = c.ISO2
    WHERE OrganisationId = @OrganisationId


    Durga

    Monday, June 20, 2016 7:03 AM
  • Try something like below. Make sure that the second query i.e alias Y as unique/latest street or country information for unique Organization ID.

    UPDATE dbo.OrganisationPostalAddress SET Street1= Y.Street1 , Stree2=Y.Street2 , Country= Y.Country FROM ( SELECT dbo.OrganisationProductMapping.OrganisationID AS OrganisationId FROM dbo.Organisation INNER JOIN dbo.OrganisationProductMapping ON dbo.Organisation.OrganisationID = dbo.OrganisationProductMapping.OrganisationID INNER JOIN dbo.Product ON dbo.OrganisationProductMapping.ProductID = dbo.Product.ProductID LEFT OUTER JOIN dbo.OrganisationPostalAddress ON dbo.Organisation.OrganisationID = dbo.OrganisationPostalAddress.OrganisationId Where dbo.OrganisationProductMapping.ProductID in ( '3E2D9BED-78CD-41D2-9A1B-66F495D14C60' , 'E11BF554-5F35-4F2E-B0FA-B5D03C0D18DF', 'A9258ACC-42FB-49C4-9670-683100CE742A', '84AF7199-6A2E-4DFB-8C76-E4041F3046DE') ) X INNER JOIN ( SELECT o.OrganisationId, o.Street1, o.Street2, o.City, o.State, o.Postcode, o.Telephone, o.Country, c.DisplayName, o.Email, o.Fax FROM OrganisationPostalAddress o LEFT JOIN Countries c ON o.Country = c.ISO2 ) Y ON X.OrganisationId = Y.OrganisationId

    WHERE Street1 = '' OR Street2 = '' OR Country = '' OR Street1 IS NULL OR Street2 IS NULL OR Country IS NULL



    Regards, RSingh





    Monday, June 20, 2016 11:10 AM
    Answerer