Insert update statement in Sql Query

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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