SQL Server Merge Data from Two Tables Based on Text Matches?

xqkwcwgp 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(119)

I have a table full of posts with slightly inaccurate descriptions due to an error in Excel before importing the data. I have since imported a corrected version into a new table but need to update the description in the old table based on matching one column with unique strings in the table with descriptions in the old one containing them.

So, if table A looks something like:

image
/uploads/keyword-keyword-4578.jpg
/uploads/keyword-4579-keyword.jpg
/uploads/keyword-keyword-keyword-4580.jpg

description
some incorrect description with 4578 in it somewhere
some incorrect description with 4579 in it somewhere
some incorrect description with 4580 in it somewhere

And Table B has something like

uniqueid
4578
4579
4580

description
new description
new description 2
new description 3

How should I write a query using SQL Server which will update the description in Table A with the description from Table B if the uniqueid is present in either the image or description from Table A?

There is no way to create a primary/foreign key relationship between the two tables because uniqueid is a field from the original excel sheet with no corresponding column in the posts table which is Table A.

I have tried a cross join but that just creates infinite loops or case where the data does not match up. Then I tried an inner join after seeing the first posted answer which is why I changed this question to make clear that the id number in the image occurs after different combinations of keyword and sometime before other keywords. I tried joining where title = title in both tables because the titles should be unique and matching, but that produces slightly more rows than there are in the second table. Finally, I tried using:

INNER JOIN TableA ON TableB.uniqueid like '%' + TableA.Image + '%'

That results in an infinite loop. Could I possible use CONTAINS or an index type function?

sql-server

来源：https://stackoverflow.com/questions/76134163/merge-data-from-two-tables-based-on-text-matches

1条答案

按热度按时间

5cnsuln71#

In this demo I have opted to add strings to the [uniqueid] instead of trying to derive a number from the strings in [image]. This enables a join between the 2 tables for the update. This works for the sample data, hopefully also in the real data. :

-- Create TableA
CREATE TABLE TableA (
    image VARCHAR(255),
    description VARCHAR(255)
);

-- Insert data into TableA
INSERT INTO TableA (image, description)
VALUES
('/uploads/image-4578.jpg', 'some incorrect description with 4578 in it somewhere'),
('/uploads/image-4579.jpg', 'some incorrect description with 4579 in it somewhere'),
('/uploads/image-4580.jpg', 'some incorrect description with 4580 in it somewhere');

-- Create TableB
CREATE TABLE TableB (
    uniqueid INT,
    description VARCHAR(255)
);

-- Insert data into TableB
INSERT INTO TableB (uniqueid, description)
VALUES
(4578, 'new description'),
(4579, 'new description 2'),
(4580, 'new description 3');

6 rows affected

UPDATE TableA
SET TableA.description = TableB.description
FROM TableA
INNER JOIN TableB
ON TableA.image = '/uploads/image-' + CAST(TableB.uniqueid AS VARCHAR) + '.jpg';

3 rows affected

SELECT * FROM TableA

image	description
/uploads/image-4578.jpg	new description
/uploads/image-4579.jpg	new description 2
/uploads/image-4580.jpg	new description 3

fiddle

Different Approach

As matching the strings isn't working then try stripping the image column down to just digits, not I still use it as a string match and avoid converting to integers, but you could do this if you need to.

UPDATE a
SET a.description = b.description
FROM TableA AS a
INNER JOIN TableB AS b ON SUBSTRING(a.image, PATINDEX('%[0-9]%', a.image), PATINDEX('%.%', a.image) - PATINDEX('%[0-9]%', a.image)) = try_cast(b.uniqueid as varchar)

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Merge Data from Two Tables Based on Text Matches?

1条答案

相关问题

热门标签

最新问答