Hi
Please can someone help me with the SQL syntax?. See attached dataset. I am trying to update the NULLS in 2019 for the Sales Type column with the same value as in 2020 for the relevant Customername. EG. Sales Type in 2019 needs to say Social for Customername ABC, as this is the corresponding value in 2020. This is just an example of a large table i have in a database. I tried this following script as advised by someone and it throws a syntax error.
UPDATE SalesMaster_bkup e SET [Sales Typ] = (SELECT [Sales Typ] FROM SalesMaster_bkup i WHERE e.customername = i.customername AND year = 2020) WHERE year = 2019
hi hope this helps 
UPDATE
SalesMaster_bkup e
SET
e.[Sales Typ] = f.[Sales Typ]
FROM
( SELECT * FROM SalesMaster_bkup WHERE year = 2019) e
JOIN
( SELECT * FROM SalesMaster_bkup WHERE year = 2020) f
ON
e.customername = f.customername
UPDATE e
SET [Sales Typ] = (SELECT TOP (1) [Sales Typ] FROM SalesMaster_bkup i WHERE e.customername = i.customername AND i.year = 2020 ORDER BY i.year)
FROM SalesMaster_bkup e
WHERE e.year = 2019
2 Likes
Thank you. I am getting this error...
Msg 102, Level 15, State 1.
Incorrect syntax near 'e'.
Msg 102, Level 15, State 1.
Incorrect syntax near 'f'. (Line 11)
Trying this now... its running -)
Thank you. This worked. What a star!
hi
yes its solved by Scott Pletcher .. thanks to Scott for that
I got to thinking why mine did not work ..
I did not do any testing of the SQL I came up with
I did the testing ... here is the working SQL ( syntax issues fixed )
Any reason you can think of . if this helps GREAT 
UPDATE
e
SET
e.[Sales Typ] = f.[Sales Typ]
FROM
( SELECT * FROM SalesMaster_bkup WHERE year = 2019) e
JOIN
( SELECT * FROM SalesMaster_bkup WHERE year = 2020) f
ON
e.customername = f.customername
1 Like
Thank you. It worked for me.
Hi @ScottPletcher I now have the last step to do. In salesMaster_bkup table I have missing values in the sales type for 2019. I now have these in another table in the database. How do I update the missing values in my sales master_bk_up table from the other table?
INSERT INTO dbo.SalesMaster_bkup ( customername, year, [Sales Typ] /*, ...*/ )
SELECT s2020.customername, 2019, s2020.[Sales Typ] /*, ...*/
FROM dbo.SalesMaster_bkup s2020
WHERE NOT EXISTS (
SELECT 1
FROM dbo.SalesMaster_bkup s2019
WHERE s2019.customername = s2020.customername AND
s2019.year = s2020.year AND
s2019.[Sales Typ] = s2020.[Sales Typ]
)
Thank you so much @ScottPletcher you are awesome. I shall try this. Is the s2020 the new table ?
Also, will this populate all the NULLS in the [Sales typ] column in salesMaster_bkup table where there is a match on customername in the s2020 table?
Yes, the s2020 is the new table (for year 2020), the s2019 the old one (year 2019).
The code will insert rows for the old year based on rows that exist in the new year but not in the old year.
That code will load ALL NULL [Sales Typ]s in 2020 to 2019, since a NULL can never "=" to anything.
If you want to test for matching NULLs, then you need to change the last line to:
ISNULL(s2019.[Sales Typ], '##') = ISNULL(s2020.[Sales Typ], '##')
Thanks. I have a new table that lists ALL the salesTypes corresponding to the Customername. what I am trying to do now is fill the NULLS in the SalesMaster_bkup table from the new table for that customername. In the new table, the year is not needed. Would the new script you mentioned above help with that?
Something like this then I would guess:
INSERT INTO dbo.new_sales_types ( customername, [Sales Typ] /*, ...*/ )
SELECT DISTINCT s2020.customername, s2020.[Sales Typ] /*, ...*/
FROM dbo.SalesMaster_bkup s2020
WHERE s2020.year = 2020
Thanks, so I was thinking to do it the other way around, where the sales master_bk_up NULL values are populated FROM the sales_type table?
Hopefully you can extrapolate from the existing code. I don't have DDL for either table, I can't guess what your exact set up is. You have to remember, we know NOTHING about YOUR data (except what you tell is). It would be impossible for us to.
1 Like
hi
another way is ..
is it possible for us to REMOTE desktop to Your machine ... and see
or
we can do chatting live ... at the same time
so that both of u can be on the same page !!!
is it possible to explain in excel .. see examples below ...
Thank you everyone fo your help. That may be a good idea. I think the code below may work. if not I may need to do a screenshare
Update SalesMaster_bkup set
[Sales Typ]=t.[Sales Typ]
From SalesMaster_bkup m
Join dbo.SalesType t on m.CustomerName=t.customername
Where m.[Sales Typ] is null
hi
Your Statement Works .. but syntax issues need to be fixed ..
please click arrow to the left for drop create data script
----------------------
-- create table
CREATE TABLE SalesMaster_bkup
(
Year int ,
CustomerName varchar(100),
SalesType varchar(100) null
)
create table SalesType
(
SalesType varchar(100) ,
CustomerName varchar(100)
)
GO
---------------------------
-- insert into table
insert into SalesMaster_bkup values
( 2019, 'ABC' , null ) ,
( 2020, 'ABC' , 'Social' ) ,
( 2019, 'Test1' , null ) ,
( 2020, 'ABC' , 'Retail' ) ,
( 2019, 'Another' , null ) ,
( 2020, 'Another' , 'Social')
insert into SalesType values
('OK SalesType','ABC') ,
('BBC SalesType','Test1')
go
Update m
set
m.[SalesType]=t.SalesType
From
SalesMaster_bkup m
Join
SalesType t
on
m.CustomerName=t.customername
Where
m.[SalesType] is null
