Member Avatar for Teofil

I would like to update 2 fields values from one record to a set of records in the same table:

I tryed this:

UPDATE dd SET dd.comment = y.comment, dd.author = y. author
FROM Table1 dd LEFT OUTER JOIN
(SELECT * FROM Table1 WHERE Idx=1234) y
ON dd.vin = y.vin
WHERE dd.vin like '%WLS%'

vin and idx fields are unique keys
My desire is to have the fields comment and author updated from the record which has idx=1234 to all the records whic contain in field vin 'WLS' string.
thanks in advance

Edited by Ezzaral because: Added code tags. Please use them to format any code that you post.
  • 2 Contributors
  • 2 Replies
  • 101 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Teofil
Member Avatar for padtes 
UPDATE Table1 
SET comment = isnull((select comment FROM Table1 y WHERE y.Idx=1234 and table1.vin = y.vin),comment),
author = isnull((select author FROM Table1 y WHERE y.Idx=1234 and table1.vin = y.vin), author)
WHERE vin like '%WLS%'

Check the logic, test. Close thread if this solves it.

Member Avatar for Teofil 
UPDATE Table1 
SET comment = isnull((select comment FROM Table1 y WHERE y.Idx=1234 and table1.vin = y.vin),comment),
author = isnull((select author FROM Table1 y WHERE y.Idx=1234 and table1.vin = y.vin), author)
WHERE vin like '%WLS%'

Check the logic, test. Close thread if this solves it.

Very nice solution!!!
Teo

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.