CODEWITHSUNDEEP
sqlsql-update
Ed.
Ed.

Reputation: 1

SQL Updating Syntax

The web hosting company changed my Php Myadmin version over the weekend and the update code I have used for a year to update stock in Mysql no longer works.

Orig code

update uc_product_stock x set x.stock = (select y.stock from import y where trim(x.SKU) = trim(y.SKU))

It now brings back the following error

#1242 - Subquery returns more than 1 row

Thanks in advance for any help on this.

Upvotes: 0

Views: 82

Answers (2)

JNK
JNK

Reputation: 65157

Your subquery returns multiple values, and SQL doesn't know which one you want to assign to your row.

You have to get the subquery returning single values.

Options are:

  • Make the subquery more specific (add extra WHERE filters, JOIN, etc)
  • Use a TOP clause to only return one row - SELECT TOP 1....

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

UPDATE x 
    SET x.stock = y.stock 
    FROM uc_product_stock x
        INNER JOIN import y 
            ON TRIM(x.SKU) = TRIM(y.SKU))

Upvotes: 2

Related Questions