Attempting to add a prefix to a column in SQL Server 2008

I'm trying to add prefix with a condition using 'from' x table then using where to specify the id

For example:

UPDATE tablenameX 
SET columnnameX = 'prefix' + columnnameX 
FROM tablenameY 
WHERE columnnameY = 'id'

I really don't see wheres the issue it refuses to read after columnnameX, what I mean by that is it would execute with no issue, however it ignores the argument I'm trying to use and it applies to every column entry.

I've used a lot of this type of simplistic SQL commands with no issue, I have no idea how to fix it nor do I have the knowledge how to build something that's more complex in nature - I'm a newbie so bear with me.

1 answer

  • answered 2022-01-23 02:53 Aaron Bertrand

    I'm not sure what the join criteria are because you haven't told us how X and Y are related, but a correlated join looks like this:

    UPDATE x
      SET x.columnnameX = 'prefix' + x.columnnameX
      FROM dbo.tablenameX AS x
      INNER JOIN dbo.tablenameY AS y
      ON y.< ??? something ??? > = x.< ??? something ???>
      WHERE y.columnnameY = 'id';
    

    Or like this:

    UPDATE x
      SET x.columnnameX = 'prefix' + x.columnnameX   
      FROM dbo.tablenameX AS x
      WHERE EXISTS
      (
        SELECT 1 FROM dbo.tablenameY AS y
          WHERE columnnameY = 'id'
          AND y.< ??? something ??? > = x.< ??? something ???>
      );
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum