Query for non sequential Id table to update values from a referenced table [duplicate]

Question

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. :)

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
– Sean Lange, Commented Jun 21, 2018 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
– graphene, Commented Jun 21, 2018 at 15:06
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
– graphene, Commented Jun 21, 2018 at 15:13

Thom A · Accepted Answer · 2018-06-21 14:57:11Z

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?

answered Jun 21, 2018 at 14:57

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

graphene Over a year ago

yes, got it. thank you! in which cases we can use the while loop then?

Thom A Over a year ago

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.

Collectives™ on Stack Overflow

Query for non sequential Id table to update values from a referenced table [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related