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
  • 116 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by Teofil

Dani AI

Generated

Two useful clarifications based on the thread: gave a safe correlated-subquery approach (which confirmed), but that pattern only writes values when the source row actually matches the target row and uses ISNULL to avoid overwriting with NULL. If the real goal is instead to copy the comment/author from the single row Idx=1234 to every row whose vin contains "WLS", use one of these simpler, clearer options.

Use variables (cheap and easy to read — good when the source is exactly one row):

DECLARE @srcComment NVARCHAR(MAX), @srcAuthor NVARCHAR(200);

SELECT @srcComment = comment, @srcAuthor = author
FROM Table1
WHERE Idx = 1234;

UPDATE Table1
SET comment = @srcComment, author = @srcAuthor
WHERE vin LIKE '%WLS%' AND Idx <> 1234;

Or use an UPDATE...FROM with a single-row derived source (explicit CROSS JOIN):

UPDATE t
SET comment = s.comment, author = s.author
FROM Table1 t
CROSS JOIN (SELECT comment, author FROM Table1 WHERE Idx = 1234) s
WHERE t.vin LIKE '%WLS%' AND t.Idx <> 1234;

Practical notes: confirm the source row exists before applying the UPDATE (to avoid propagating NULLs), exclude the source row when appropriate (Idx <> 1234), and preview affected rows with a SELECT using the same WHERE. LIKE '%WLS%' is not sargable — expect a scan on large tables; consider a more selective predicate or an indexed computed column if this runs often. Wrap the UPDATE in a transaction on production systems and test on a copy first.

Recommended Answers

All 2 Replies

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.