0

I need to update a column in a table X with values that are written in another table Y. Mind you that there are gaps in the Id column as several/some rows were deleted. It starts with 63450 the Id column in table X, and it is not really sequential as shown below:

table X

    Id      Name                 Value
-------------------------------------------
    63450   cmd                   NULL 
    63451   Jong                  NULL 
    63456   Xau-Min               NULL 
    63457   bgf                   NULL 
    63458   tcr                   NULL 
    63459   cro                   NULL 
    63500   344453f               NULL 
    63501   stackoverflow         NULL

Table Y (parametrization)

Id  Acronym       Code
-------------------------------
1    cmd         545654
2    bgf         454565
3    cro         555555
4    rtg         465456
5    ert         546546
6    tcr         878787

Now after updating table X it should appear the following. It will update the vALUE in table X according to the matches in table Y...

table X after updating.... 

    Id      Name                  Value
-------------------------------------------
    63450   cmd                   545654
    63451   Jong                  NULL 
    63456   Xau-Min               NULL 
    63457   bgf                   454565
    63458   tcr                   878787
    63459   cro                   555555
    63500   344453f               NULL 
    63501   stackoverflow         NULL

if I try with

  USE Database
   DECLARE @counter int
   SET @counter=(select count(*) from table_X)
       WHILE @counter>0
             BEGIN
                  UPDATE table_X
                  SET Value=(select Code                                       
                             from table_Y b inner join table_X a on a.Name=b.Acronym 
                             where a.Id= max(a.Id)-@counter+1)
                  SET @value=@value-1
             END

it WILL not work as the Id is not sequential... how to achieve the updated table X as shown?

2nd: it would be nice to have a function to detect only letters (in the column Name of table X).. is there any in SQL? I only know to detect numeric values in strings: the isnumeric() function.

Thanks. :)

graphene
  • 109
  • 1
  • 8
  • 1
    I don't understand. The looping thing and parsing strings makes no sense from what you posted. Why not simply join those two tables on the name and acronym columns? – Sean Lange Jun 21 '18 at 14:55
  • you are right. I fell in the pitfall to use a while loop and a simple join did it. Thanks Sean and Larnu. – graphene Jun 21 '18 at 15:06
  • Use: where X.[Name] like '%[^0-9]%' to exclude non-numbers – cloudsafe Jun 21 '18 at 15:10
  • the %[^0-9]%' does not work in sql server I'm afraid for what I wanted. I have tested your suggestion and it also delivers strings with numbers. I would like to have only letters without any numbers. That regular expression does not work at least in the version I'm using for sql server. – graphene Jun 21 '18 at 15:13

1 Answers1

4

I think this is what you're after:

UPDATE X
SET [Value] = Y.Code
FROM TableX X
     JOIN TableY Y ON X.[name] = Y.Acronym;

There's definitely no need for a loop here. In fact, using a looping structure in SQL is generally the worst thing to do for performance.

This is actually quite a simple query, in terms of SQL statements, so do you understand what it's doing?

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • yes, got it. thank you! in which cases we can use the while loop then? – graphene Jun 21 '18 at 15:00
  • 2
    Generally, whenever you're doing something that can't be achieved at a dataset level. Maybe, for example, you need to send some data derived emails via `sp_send_dbmail`, and different recipients needs different data. Most "things" that people tend to use loops/cursors for can actually be done in a dataset manner, and are (more than often) quicker. – Thom A Jun 21 '18 at 15:04