Complex UPDATE, how to do?

SQL
1

Hi there,

In the SQL course, module 4 (Inserting, Updating, and Deleting Data) lesson 8 (Using Subqueries in Updates), Mosh shows an example of updating the orders table adding a comment saying that the customer is Gold.

The tables are something like this:

ORDER:
order_id customer_id comments (and more...)

CUSTOMERS:
customer_id name points (and more...)

So, the idea is to update the column comments in the orders table adding ‘Gold Customer’ for all the orders by the gold customers.

The solution is:

UPDATE orders
SET	comments = 'Gold Customer'
WHERE customer_id IN(
	SELECT customer_id
	FROM customers
	WHERE points > 3000
);

So I want to improve a little this and not only add the string “Gold Customer”, but also add how many points the customer has. The string would be “Gold Customer with 3200 points” for instance…

How can I achieve this?

2

I feel like you might need a JOIN for that.

UPDATE orders o
INNER JOIN customers c
ON c.id = o.customer_id
SET o.comments = CONCAT("Gold Customer with ", c.points, " points")
WHERE c.points > 3000;
1 Like
3

Great it worked smoothly. Thank you :slight_smile: