Update a record from another record in same table 1

[djdidge]

UPDATE b
SET ProductName = t .ProductName
FROM tblProductRecord t INNER JOIN
tblProductRecord b ON t .ProductUID = b.ProductUID
WHERE t .ProductUID = 1887 AND b.ProductUID = 1884



This almost works.... not quite!

Ideas??!

 

[hmckillop]

dont think you can do it using the inner join syntax due to your where condition,
You could do it like this

UPDATE   tblProductRecord  
SET         ProductName = (SELECT ProductName FROm tblProductRecord WHERE ProductUID = 1887 )
WHERE     ProductUID = 1884

"I'm living so far beyond my income that we may almost be said to be living apart

 

[djdidge]

Hi there,

Thanks for your reply... I simplified the SET set due to the huge list!... lets assume I have 2 fields to SET. Can that be done with 1 select statement? intead of having to run a query for each field.

Regards,

 

[hmckillop]

You can join like this example to manage multiple fields. i used northwind database products table as the example, but essentially the same

/* --data from both rows prior update
1	Chang	1	1	10 boxes x 20 bags	18.0000	39	0	10	0
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.0000	13	70	25	0
*/

--update statement
UPDATE b
SET ProductName = p.ProductName, 
	QuantityPerUnit = p.QuantityPerUnit
FROM Products b
INNER JOIN (select productid, productname, QuantityPerUnit from products where productid = 3) p ON p.productid = 3
WHERE b.productid = 1

/* -- data in both rows after update
1	Aniseed Syrup	1	1	12 - 550 ml bottles	18.0000	39	0	10	0
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.0000	13	70	25	0
*/

[code]
This effectively allows you to join to the row without an "actual join" occurring : note the ON clause doesnt reference the outer table. Be wary though this should only be used when updating a single row (as indicated by the WHERE clause) as it will cause multiple updates with same data 

"I'm living so far beyond my income that we may almost be said to be living apart

 

[djdidge]

Thx chap... ill give that a go!

 

[djdidge]

Works a treat thx!

UPDATE b
SET ProductName = t .ProductName, TypeUID = t .TypeUID, CampaignUID = t .CampaignUID, PMUID = t .PMUID, PPUID = t .PPUID, StartDate = t .StartDate,
EndDate = t .EndDate, ChargeUID = t .ChargeUID, Objective = t .Objective, Measure = t .Measure, Confidential = t .Confidential, Notes = t .Notes,
Fellows = t .Fellows, Chartered = t .Chartered, Technical = t .Technical, TechnicalTrainees = t .TechnicalTrainees, Trainees = t .Trainees,
Students = t .Students, Affiliates = t .Affiliates, Retired = t .Retired, Members = t .Members, Consumers = t .Consumers, Business = t .Business,
Government = t .Government, Professionals = t .Professionals, Prospective = t .Prospective, Academia = t .Academia, Employers = t .Employers,
Media = t .Media, Completed = t .Completed, DateTimeAdded = t .DateTimeAdded, DateTimeModified = t .DateTimeModified, CreatorID = t .CreatorID,
Deleted = t .Deleted, DateDelivered = t .DateDelivered, LifeExpect = t .LifeExpect, BookID = t .BookID, EBMSid = t .EBMSid, FoundID = t .FoundID,
eBriefFreq = t .eBriefFreq, Publically = t .Publically, Cancelled = t .Cancelled, NumberAtPublish = t .NumberAtPublish
FROM tblProductRecord b INNER JOIN
(SELECT ProductUID, ProductName, TypeUID, CampaignUID, PMUID, PPUID, StartDate, EndDate, ChargeUID, Objective, Measure, Confidential, Notes, Fellows,
Chartered, Technical, TechnicalTrainees, Trainees, Students, Affiliates, Retired, Members, Consumers, Business, Government,
Professionals, Prospective, Academia, Employers, Media, Completed, DateTimeAdded, DateTimeModified, CreatorID, Deleted,
DateDelivered, LifeExpect, BookID, EBMSid, FoundID, eBriefFreq, Publically, Cancelled, NumberAtPublish
FROM tblProductRecord
WHERE (ProductUID = 1888)) t ON t .productuid = 1888
WHERE b.productuid = 1884