How to update column of one table form another table

Transact-SQL
1

TABLE A TABLEB
ID Model ID Segment
1 FAN 1
1 FAN 2 FAN
1 Mouse 3
1 FAN
2 FAN
2 FAN
2 FAN
2 FAN

image

If all the model for TABLE A is FAN for Specific ID like in above case all MODEL are FAN for ID 2.
I want to update TABLEB Segment value to FAN with ID 2. if all value is not fan i dont want to update
TABLEB.

Thanks a lot in advance for help.

2

hi

hope this helps

this is one way of several ways to do this

drop table TABLEA 

create table TABLEA 
(
ID int , 
Model  varchar(10)
)
insert into TABLEA select 1,'FAN' 
insert into TABLEA select 1,'FAN'
insert into TABLEA select 1,'Mouse'
insert into TABLEA select 1,'FAN'
insert into TABLEA select 2,'FAN'
insert into TABLEA select 2,'FAN'
insert into TABLEA select 2,'FAN'
insert into TABLEA select 2,'FAN'

drop table TABLEB

create table TABLEB
(
ID int ,
Segment varchar(10) null 
)

insert into TableB select 1,null
insert into TableB select 2 ,null
insert into TableB select 3,null

;with cte as 
(
    select 
	       id
		 , count(distinct model) as cnt 
	from 
	   TableA 
    group by 
	   id 
    having 
	   count(distinct model) = 1 
)
update b set 
  b.segment = a.Model
from 
  TABLEA a 
    join 
  TableB b  
      on a.ID = B.ID 
    join 
  cte c 
     on a.ID = c.ID

image

3

Please provide consumable test data in future:

CREATE TABLE #a
(
	ID int NOT NULL
	,Model varchar(20) NOT NULL
);
INSERT INTO #a
VALUES (1, 'FAN'), (1, 'FAN'), (1, 'Mouse'), (1, 'FAN')
	,(2, 'FAN'), (2, 'FAN'), (2, 'FAN'), (2, 'FAN'), (2, 'FAN');
CREATE TABLE #b
(
	ID int NOT NULL
	,Segment varchar(20) NOT NULL
);
INSERT INTO #b
VALUES (1, ''), (2, ''), (3, '');

You could use the TSQL version of UPDATE which allows a join but unfortunately the result is indeterminate if the source has multiple values for each condition.
It is safer to use MERGE:

MERGE #b T
USING
(
	SELECT ID, MAX(Model) AS Model
	FROM #a
	GROUP BY ID
	HAVING COUNT(DISTINCT Model) = 1
) S
	ON T.ID = S.ID
WHEN MATCHED
	THEN UPDATE
	SET Segment = S.Model;

select * from #b
4

I've found MERGE to be slow and quirky, I'd use a "standard" UPDATE w/ JOIN here.

UPDATE B
SET Segment = A.Model
FROM #TABLEB B
INNER JOIN (
    SELECT ID, MAX(Model) AS Model
    FROM #TABLEA
    GROUP BY ID
    HAVING MIN(Model) = MAX(Model)
) AS A ON A.ID = B.ID
5

Thanks for your reply. I want to check with specific text value, where ever all text value is only FAN i want to update TABLE B.
I don't want with having count(distinct model) = 1
Many thanks in advance

6

The fact that you have not accepted any of the 3 answers as a solution provided by knowledgeable devs indicates that your question is not clear. Please post the desired output in the form of an image or ddl and dml

7

Actually that was stated in the original q, I (we) just ignored it.

UPDATE B
SET Segment = A.Model
FROM #TABLEB B
INNER JOIN (
    SELECT ID, MAX(Model) AS Model
    FROM #TABLEA    
    GROUP BY ID
    HAVING MAX(Model) = 'FAN' AND MIN(Model) = MAX(Model)
) AS A ON A.ID = B.ID
8

thanks a lot its working.

1 Like
9

True; MERGE is badly implemented in SQL Server, especially with regards to locking and the subsequent blocking, but in my experience it is not too bad if used only for UPDATES.

The problem with UPDATE JOIN is that it allows indeterminate values to be updated.
The following is a simple example to update a header table with the minimum value in a detail table.
With this test data:

CREATE TABLE #h
(
	hid int NOT NULL PRIMARY KEY
	,MinDValue int NULL
);
INSERT INTO #h(hid) VALUES (1),(2),(3);

CREATE TABLE #d
(
	hid int NOT NULL
	,DValue int NOT NULL
);
INSERT INTO #d
VALUES (1, 1), (1, 2)
	,(2, 1), (2, 2)
	,(3, 1), (3, 2);

the following will appear to work:

UPDATE H
SET MinDValue = D.DValue
FROM #h H
	JOIN #d D
		ON H.hid = D.hid;

as the UPDATE statement will take the first value which, with a small table under low loading, will always be in the order the data is entered.

Now we know that a table is an unordered set and the UPDATE statement is wrong but do you want to reply on all your developers knowing this or do you want 'random bugs' in production code?

If they tried that with MERGE:

MERGE #h H
USING
(
	SELECT hid, DValue
	FROM #d
) S
	ON H.hid = S.hid
WHEN MATCHED
	THEN UPDATE
	SET MinDValue = S.DValue;

they would get an informative exception and be forced to write:

MERGE #h H
USING
(
	SELECT hid, MIN(DValue) AS DValue
	FROM #d
	GROUP BY hid
) S
	ON H.hid = S.hid
WHEN MATCHED
	THEN UPDATE
	SET MinDValue = S.DValue;

If you really object to MERGE you could get them to have a working MERGE statement as a comment before writing the UPDATE statement.

Microsoft will never change the way UPDATE works but they could add a global STRICT type option sometime to give the 'statement attempted to UPDATE the same row more than once' exception.

10

Well, any time you UPDATE a table where multiple rows can match, you face that risk. That's your own fault, your own poor code.

MERGE is much slower doing a standard UPDATE than an UPDATE statement. MS themselves state that now in their documentation.

My preference is still to avoid MERGE unless I need all three capabilities (DELETE, INSERT, UPDATE) or the table is extremely large and separate UPDATE and INSERT statements would each have to fully scan the table.