Strange data swapping error occurs when I attempt to update rows in my table from another table in my sql database...please help!

Question

So I have a table of data that is 10,000 lines long. Several of the columns in the table simply describe information about one of the columns, meaning, that only one column has the content, and the rest of the columns describe the location of the content (its for a book). Right now, only 6,000 of the 10,000 rows' content column is filled with its content. Rows 6-10,000's content column simply says null.

I have another table in the db that has the content for rows 6,000-10,000, with the correct corresponding primary key which would (seemingly) make it easy to update the 10,000 row table.

I have been trying an update query such as the following:

UPDATE table(10,000)
SET content_column = (SELECT content FROM table(6,000-10,000) WHERE table(10,000).id = table(6-10,000.id)

Which kind of works, the only problem is that it pulls in the data from the second table just fine, but it replaces the existing content column with null. So rows 1-6,000's content column become null, and rows 6-10,000's content column have the correct values...Pretty strange I thought anyway.

Does anybody have any thoughts about where I am going wrong? If you could show me a better sql query, I would appreciate it! Thanks

Answer 1

The reason is that you have no Where clause so all rows are being updated. The following query will only update tows that exist in the 6K-10K table, but will still overwrite matching values.

Update table(10,000)
Set content_column =    (
                        Select content 
                        From table(6,000-10,000) 
                        Where table(10,000).id = table(6-10,000.id)
                        )
Where Exists    (
                Select 1
                From table(6,000-10,000) 
                Where table(10,000).id = table(6-10,000.id)
                )

Another way, if you simply do not want to overwrite the existing values would be to use Coalesce:

Update table(10,000)
Set content_column =    Coalesce(table(10,000).content_column
                            ,   (
                                Select content 
                                From table(6,000-10,000) 
                                Where table(10,000).id = table(6-10,000.id)
                                ))