Write an update query based on results from another record with an inner join.

Need help with a simple update query, hoping to get a better way than creating temp tables etc...
When I run this select:

SELECT A.fd1, A.fd2, B.fd3
      from A inner join B on A.fd1 = B.fd3

Get results like:

A.fd1      A.fd2            B.fd3
X                                    528
X                 1                      528
X                 2                      400
X                 3                      528
X                 4                      528
X                 5                      528
Y                                     600
Y                 1                      600
Y                 2                      600
Y                 3                       600
Y                 4                      350

Need to update B.fd3 on the records with a value in A.fd2 to equal B.fd3 on records with no value on A.fd2. End result:

A.fd1      A.fd2            B.fd3
X                                    528
X                 1                      528
X                 2                      528
X                 3                      528
X                 4                      528
X                 5                      528
Y                                     600
Y                 1                      600
Y                 2                      600
Y                 3                       600
Y                 4                      600

Thanks!
Charlene KerrDirector, Principal ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sebastian StrittmatterSoftware DevelopmentCommented:
Should B.fd3 updated to a static value or what are the criterias for the B.fd3. This is the condition for the join. To set this right you need an update criteria.
0
Charlene KerrDirector, Principal ConsultantAuthor Commented:
All values in B.fd3 for any given value in A.fd1 need to be the same. The update should take the value for the record with no value in A.fd2 and apply it to the rest of the records for the same value in A.fd1, regardless of the values in A.fd2.

IF you are familiar with work breakdown structure, you can look at the first record, the one with no value in A.fd2, as the top level of the structure. The following records (those with values in the A.fd2 field) are sublevel of the same structure. They all need to be the same as the top level value in B.fd3.

Hope that helps
0
PortletPaulEE Topic AdvisorCommented:
The example doesn't make sense

SELECT A.fd1, A.fd2, B.fd3
      from A inner join B on A.fd1 = B.fd3

but at no time is A.fd1 = B.fd3 in the sample

A.fd1      A.fd2            B.fd3
X                                    528
X                 1                      528
X                 2                      400

Can you please ensure the example is consistent with the query (or vice-versa).
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Charlene KerrDirector, Principal ConsultantAuthor Commented:
My apologies, thanks for catching that. It should be:

SELECT A.fd1, A.fd2, B.fd3
      from A inner join B on A.fd1 = B.fd1

B.fd1 is not displayed - it is the key field that matches A.fd1.
0
PortletPaulEE Topic AdvisorCommented:
Ok. thanks.

can you now show us the rows of each table independently? i.e. some rows of A and separately some rows of B

reason for asking is I still don't see how that result can be produced, how does the following occur?

A.fd1      A.fd2            B.fd3
X                                    528
X                 1                      528
X                 2                      400 <<< how is this possible on only one row?
0
Scott PletcherSenior DBACommented:
Waiting at work for something to complete, so I have time now to post some quick code:

UPDATE B_alias
SET fd3 = A_main.fd3
FROM A
INNER JOIN B B_alias ON A.fd1 = B_alias.fd1 AND A.fd2 > ''
INNER JOIN (
        SELECT A.fd1, MAX(B.fd3) AS fd3 /* just in case there's somehow more than 1 row*/
        FROM A
        INNER JOIN B ON A.fd1 = B.fd1 AND (A.fd2 = '' OR A.fd2 IS NULL) /*not sure if fd2 is blank or NULL, change to match your actual data as needed*/
        GROUP BY A.fd1  /*just in case there's somehow more than 1 row*/
) AS A_main ON A_main.fd1 = B.fd1
0
Charlene KerrDirector, Principal ConsultantAuthor Commented:
For PortletPaul:

Table A:                    
fd1     fd2  
XX                
XX       12      
XX       22      
XX       32      
YY                
YY       12      
YY        22    


Table B
fd1     fd2   fd3
XX                500
XX       12      500
XX       22      222
XX       32      222
YY                400
YY       12      400
YY        22     200

So the fd3 field on Table B needs to be the same for all equal values on fd1, and the update is based on the record with no value in fd2.
The reason for the join to Table A is that there are other fields involved in the where statement.

Hope this helps
0
PortletPaulEE Topic AdvisorCommented:
sorry - under review
0
PortletPaulEE Topic AdvisorCommented:
--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE TableA(
   fd1 VARCHAR(18) 
  ,fd2 VARCHAR(30)
);
go
INSERT INTO TableA(fd1,fd2) VALUES ('XX',NULL);
INSERT INTO TableA(fd1,fd2) VALUES ('XX','12');
INSERT INTO TableA(fd1,fd2) VALUES ('XX','22');
INSERT INTO TableA(fd1,fd2) VALUES ('XX','32');
INSERT INTO TableA(fd1,fd2) VALUES ('YY',NULL);
INSERT INTO TableA(fd1,fd2) VALUES ('YY','12');
INSERT INTO TableA(fd1,fd2) VALUES ('YY','22');
go
CREATE TABLE TableB(
   fd1  VARCHAR(2) 
  ,fd2 INTEGER
  ,fd3 INTEGER 
);
go
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('XX',NULL,500);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('XX',12,500);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('XX',22,222);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('XX',32,222);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('YY',NULL,400);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('YY',12,400);
INSERT INTO TableB(fd1,fd2,fd3) VALUES ('YY',22,200);
go

Open in new window

select A.fd1, A.fd2, B.fd3
from tableA A 
inner join TableB B on (A.fd1 = B.fd1 and a.fd2 is null and b.fd2 is null) 
                     or (A.fd1 = B.fd1 and a.fd2 = b.fd2)
order by 1, 2, 3

Open in new window

http://rextester.com/IMJA53068
0
PortletPaulEE Topic AdvisorCommented:
I believe this update will do what you need:

with cte as (
        select *
        from tableB b
        cross apply (select tableB.fd3 new_val from tableB where b.fd1 = tableB.fd1 and tableb.fd2 is null) ca
        )
update cte
set fd3 = new_val
;

Open in new window

result
       fd1   fd2    fd3  
 ---- ----- ------ ----- 
   1   XX    NULL   500  
   2   XX    12     500  
   3   XX    22     500  
   4   XX    32     500  
   5   YY    NULL   400  
   6   YY    12     400  
   7   YY    22     400  

Open in new window


see: http://rextester.com/XNS94924
0
awking00Information Technology SpecialistCommented:
If you could provide some sample data for both tables and what you expect as a result of the update, it would be most helpful. I'm not sure at this point which field you are trying to update, A.fd3 or B.fd3.
0
Mark WillsTopic AdvisorCommented:
Using Paul's data above (thanks Paul), we can do :
update B set fd3 = BB.fd3
from TableB B
inner join TableA A on A.fd1 = B.fd1 and isnull(A.fd2,'') = isnull(B.fd2,'')
inner join TableB BB on BB.fd1 = B.fd1 and BB.fd2 is NULL

Open in new window

Even though fd2 is integer, we can still use isnull(fd2,'') in the joins. You could be more comfortable with isnull(fd2,0) but doesnt matter, we are simply creating a join condition needing equality. Ideally we dont need to include the NULL handling, because it really isnt needed for the 'other' rows in TableB - because those NULL rows are the source for updating (not the target of update). See comments in deliberations below....

 And the deliberation is embodied in comments contained in the code snippet below - after creating test data - so please read through  and ask any follow on questions:
-- Create Test Data

CREATE TABLE #TableA(
   fd1 VARCHAR(18) 
  ,fd2 VARCHAR(30)
);
go
INSERT INTO #TableA(fd1,fd2) VALUES ('XX',NULL);
INSERT INTO #TableA(fd1,fd2) VALUES ('XX','12');
INSERT INTO #TableA(fd1,fd2) VALUES ('XX','22');
INSERT INTO #TableA(fd1,fd2) VALUES ('XX','32');
INSERT INTO #TableA(fd1,fd2) VALUES ('YY',NULL);
INSERT INTO #TableA(fd1,fd2) VALUES ('YY','12');
INSERT INTO #TableA(fd1,fd2) VALUES ('YY','22');
go
CREATE TABLE #TableB(
   fd1  VARCHAR(2) 
  ,fd2 INTEGER
  ,fd3 INTEGER 
);
go
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('XX',NULL,500);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('XX',12,500);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('XX',22,222);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('XX',32,222);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('YY',NULL,400);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('YY',12,400);
INSERT INTO #TableB(fd1,fd2,fd3) VALUES ('YY',22,200);
go

-- Lets get started on the deliberations.... 

-- if I use the Asker supplied query, I get quite a different result....

select a.fd1 as AFD1, A.fd2 as AFD2, B.fd1 as BFD1, b.* 
from #TableA A
inner join #TableB B on A.fd1 = B.fd1


-- so we need fd2 in there. Because it is an inner join, we can put tableB first and join to tableA
-- now there is a small problem... Because fd2 can be NULL, then it doesnt join properly

select a.fd1 as AFD1, A.fd2 as AFD2, B.fd1 as BFD1, b.* 
from #TableB B
inner join #TableA A on A.fd1 = B.fd1 and A.fd2 = B.fd2

-- so we have to 'fix' that NULL condition (maybe)

select a.fd1 as AFD1, A.fd2 as AFD2, B.fd1 as BFD1, b.* 
from #TableB B
inner join #TableA A on A.fd1 = B.fd1 and isnull(A.fd2,'') = isnull(B.fd2,'')

-- we now have all the rows properly expanded, we now need to incorporate the desired value from TableB

select a.fd1 as AFD1, A.fd2 as AFD2, B.fd1 as BFD1, BB.fd3 as NEWFD3 
from #TableB B
inner join #TableA A on A.fd1 = B.fd1 and isnull(A.fd2,'') = isnull(B.fd2,'')
inner join #TableB BB on BB.fd1 = B.fd1 and BB.fd2 is NULL

-- now the irony of fd2 being NULL is we dont really need it
-- unless of course, that row has critical information needed in the WHERE clause
-- if not needed for the where clause, then the irony is we dont have to manage the NULL state, 
-- and not needed for the update because it ends up being the source value

select a.fd1 as AFD1, A.fd2 as AFD2, B.fd1 as BFD1, BB.fd3 as NEWFD3 
from #TableB B
inner join #TableA A on A.fd1 = B.fd1 and A.fd2 = B.fd2
inner join #TableB BB on BB.fd1 = B.fd1 and BB.fd2 is NULL


-- which leaves us with the UPDATE (keeping the fd2 management for now, and should be avoided - see above)

update B set fd3 = BB.fd3
from #TableB B
inner join #TableA A on A.fd1 = B.fd1 and isnull(A.fd2,'') = isnull(B.fd2,'')
inner join #TableB BB on BB.fd1 = B.fd1 and BB.fd2 is NULL

-- and lets check TableB

select * from #TableB

-- Job done, let's clean up....

-- Drop Table #TableA
-- Drop Table #TableB

Open in new window

Hope you find the above deliberation of interest, and if Table2.fd2 = NULL doesnt carry any information you need for the subsequent WHERE clause, then you dont need to manage that isnull() on the joins. Does that make sense ?
And welcome to EE - I see this is your first question :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Can you also explain what you mean in this sentence? -
>>They all need to be the same as the top level value in B.fd3.<<
Is that the max value of fd3 (for each fd1), the value of fd3 where fd2 is smallest or the value of fd3 where fd2 is null in table B?
0
Scott PletcherSenior DBACommented:
One final thing, for max efficiency, I should have checked to make sure an UPDATE was actually needed, that is, that the new column value was actually different from the original.

UPDATE B_alias
SET fd3 = A_main.fd3
FROM A
INNER JOIN B B_alias ON A.fd1 = B_alias.fd1 AND A.fd2 > ''
INNER JOIN (
        SELECT A.fd1, MAX(B.fd3) AS fd3 /* just in case there's somehow more than 1 row*/
        FROM A
        INNER JOIN B ON A.fd1 = B.fd1 AND (A.fd2 = '' OR A.fd2 IS NULL) /*not sure if fd2 is blank or NULL, change to match your actual data as needed*/
        GROUP BY A.fd1  /*just in case there's somehow more than 1 row*/
) AS A_main ON A_main.fd1 = B.fd1
WHERE B_alias.fd3 <> A_main.fd3


Also, an an FYI, never use ISNULL() in a join, such as:
inner join #TableA A on A.fd1 = B.fd1 and isnull(A.fd2,'')
because it prevents SQL from taking full advantage of certain indexes (in tech terms, it's "non-sargable").

Instead, use the style I used above:
(A.fd2 = '' OR A.fd2 IS NULL)
0
Mark WillsTopic AdvisorCommented:
Never say never :)

And of course, the use of isnull() in joins, could muck up use of indexes. Indeed, any function on a predicate (used in JOINs/ON or WHERE) should always be avoided.

I did attempt to point out that the handling of the NULL fd2 rows is probably not needed, because those rows represent the data source for the updates, not the target of the updates.

I should (?) have pressed that point more forcefully (rather than "irony").

And you are quite correct, if the fd2 columns are empty strings (zero length character based columns) then any checking for NULL is superfluous (especially in the join) . Which would make the final update more like :
update B set fd3 = BB.fd3
from TableB B
inner join TableA A on A.fd1 = B.fd1 and A.fd2 = B.fd2
inner join TableB BB on BB.fd1 = B.fd1 and BB.fd2 = ''
where b.fd3 <> bb.fd3

Open in new window

0
Scott PletcherSenior DBACommented:
if the fd2 columns are empty strings (zero length character based columns) then any checking for NULL is superfluous (especially in the join)

Which I mentioned in my initial post of my query.

As I understand the data, there is no "B.fd2", fd2 is only in the A table.


I stand by "never" for that.  You can (almost) always avoid using ISNULL (*), and it will always prevent best index searches.
As another example, to allow a NULL value to mean "always match", some developers think it's clever to do this:

ISNULL(@param_to_compare_to_col1, col1) = col1

But that's horrible for performance.  Much better is the clearer alternative, without ISNULL:

WHERE (@param_to_compare_to_col1 IS NULL OR @param_co_compare_to_col1 = col1)

(*) The exceptions where it's vastly easier to use ISNULL() are so rare that in a practical sense you can ignore them.
0
Mark WillsTopic AdvisorCommented:
Back in  #a42698293  the Author did clarify with some sample data and shows  fd2 is in TableB...


And IF fd1 and fd2 are part of an index, it will probably still use index scan, where as, ideally we want index seek, which is far more likely if it was simply a join on fd1 and fd2. Almost certainly an index scan if joined on fd1 and isnull(fd2,''). The optimizer will prefer to index scan than (say) table scan, because it has the clues of fd1 and fd2 regardless.

However, depending on other predicates for TableA (the elusive WHERE for example), that join could easily lapse into table scan. Most definitely undesirable for overall performance.

And yet, we have no real idea on the actual tables / indexes / size / additional predicates etc. So, still it is hard to ascertain (which probably lends more support to your case).

So... Agree wholeheartedly much better to not use isnull(fd2,'')
0
PortletPaulEE Topic AdvisorCommented:
If using inner joins is really necessary (which I don't see) then you can pair the fd2 NULL rows without using ISNULL or COALESCE  and for any approach you can include a not equal condition as suggested by Scott
e.g.
select
*
from TableB B
inner join TableA A on A.fd1 = B.fd1 and A.fd2 = B.fd2
inner join TableB BB on BB.fd1 = B.fd1 and BB.fd2 is NULL
where B.fd3 <> BB.fd3
;



update B set fd3 = BB.fd3
from TableB B
inner join TableA A on A.fd1 = B.fd1 and A.fd2 = B.fd2
inner join TableB BB on BB.fd1 = B.fd1 and BB.fd2 IS NULL
where B.fd3 <> BB.fd3
;

Open in new window

select (not equal)
+---+-----+-----+-----+-----+-----+-----+------+-----+
|   | fd1 | fd2 | fd3 | fd1 | fd2 | fd1 | fd2  | fd3 |
+---+-----+-----+-----+-----+-----+-----+------+-----+
| 1 | XX  |  22 | 222 | XX  |  22 | XX  | NULL | 500 |
| 2 | XX  |  32 | 222 | XX  |  32 | XX  | NULL | 500 |
| 3 | YY  |  22 | 200 | YY  |  22 | YY  | NULL | 400 |
+---+-----+-----+-----+-----+-----+-----+------+-----+

after update
+---+-----+------+-----+
|   | fd1 | fd2  | fd3 |
+---+-----+------+-----+
| 1 | XX  | NULL | 500 |
| 2 | XX  | 12   | 500 |
| 3 | XX  | 22   | 500 |
| 4 | XX  | 32   | 500 |
| 5 | YY  | NULL | 400 |
| 6 | YY  | 12   | 400 |
| 7 | YY  | 22   | 400 |
+---+-----+------+-----+

Open in new window

see  https://rextester.com/FTNKN98537

Regrettably we cannot know from the available sample data if it really contains NULLs or empty strings.
I have assumed NULLs but if that is untrue then replace IS NULL with = '' as needed.

or, perhaps  cover both with:
inner join TableB BB on BB.fd1 = B.fd1 and ( BB.fd2 IS NULL or BB.fd2 = '' )

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.