UPDATE Table Based on Data in Another Table

UPDATE Table Based on Data in Another Table

I have two related tables. I need to go and update atable searching for a value I find in the second table (byjoining both tables). I wrote the following query, but endedup with a syntax error:

UPDATE Software, PckgSET Software.packid=’DB34’WHERE Software.packid=Pckg.packid AND      Pckg.packname=’Manta’ AND      Pckg.packver=1.50;
Software and Pckg are related by packid. Manta is the nameof a non-existent database package, version 1.50.I can’t figure out why this query is not working. Can you help?

Try an UPDATE statement like this instead:

UPDATE Software    SET packid=’DB34′    WHERE packid IN        (SELECT packid FROM Pckg        WHERE packname=’Manta’ AND        packver=1.50);
The UPDATE statement can operate on only one table at a time, so you use a subquery construction to find the data from the other table.

Thanks to Joe Celko for an additional solution to this problem:The SQL-92 standard allows you to use scalar subquery expressions anywhere you would use a scalar value. This can be handy for updating one table based on the contents of another.

For example, let’s update the prices of software based on a new catalog we just got. Assume that item is the key in both tables:

UPDATE Software    SET price = (SELECT price        FROM NewCatalog AS N1        WHERE Software.item = N1.item)    WHERE EXISTS (SELECT price        FROM NewCatalog AS N1        WHERE Software.item = N1.item);
Here are the important points to remember about this statement:

1) An UPDATE statement cannot use a correlation name or more than one table name in the UPDATE clause. Many vendors allow a correlation name, however. The table name which appears in the UPDATE clause has scope over the rest of the statement.

2) The scalar subquery expression has to return zero or one value; if it returns more than that, you will get an error message and the transaction will rollback. One way to be sure that you always get a single value is to use an aggregate function in the scalar subquery expression.

3) If the scalar subquery expression returns no value (an empty result set), then it is a NULL. This NULL will be assigned to the row in the table being updated.

4) If you want to update only the rows that match a row in the second table, then you have to do what I have shown here. You must add a WHERE clause to the UPDATE statement that checks for the existence of the match.

The best way to do this is not always clear. Some SQL products will run faster if the SELECT clause in an EXISTS predicate has a constant in it, others run faster if it has a SELECT * in it, and others will run faster if it is an exact copy of the scalar subquery expression you used in the SET clause.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as