CODEWITHSUNDEEP
mysqljoin
agerber85
agerber85

Reputation: 73

MySQL UPDATE records referenced by another table

I need to update a specific column of all records whose primary key is referenced by a specific column in another table. (The foreign key relationship is already set up.) An analog to what I'm trying to do would be this:

Table 1: Sandwiches

Sandwich(varchar) [Primary Key] Requested(bool)
Salami 1
Pastrami Null
Ham 0
Turkey 1

Table 2: Requests

Character(varchar) Sandwich (varchar) [Foreign Key to Sandwiches]
Charlie Ham
Linus Turkey
Marcy Ham
Lucy Salami
Snoopy Pastrami

I want every null value in sandwiches.requested updated to 1 if there is a record in requests referencing it's primary key. E.g., since Pastrami has a null value for sandwiches.requested and Snoopy requested Pastrami, I want sandwiches.requested updated to 1 for Pastrami, so that Sandwiches looks thus:

Table 1: Sandwiches

Sandwich(varchar) [Primary Key] Requested(bool)
Salami 1
Pastrami 1
Ham 0
Turkey 1

I know how to:

SELECT SANDWICHES.SANDWICH, SANDWICHES.REQUESTED FROM SANDWICHES INNER JOIN REQUESTS ON SANDWICHES.SANDWICH = REQUESTS.SANDWICH WHERE REQUESTED = NULL

which returns:

Sandwich Requested
Pastrami Null

telling me which ones I want to update, but say my data set were too large to manually perform all the updates. How would I create an update command to update every match?

Upvotes: 0

Views: 61

Answers (2)

Salman ansari
Salman ansari

Reputation: 154

Update SANDWICHES JOIN REQUESTS ON SANDWICHES.SANDWICH = REQUESTS.SANDWICH set SANDWICHES.REQUESTED = 1 WHERE SANDWICHES.REQUESTED = NULL

Upvotes: 1

ysth
ysth

Reputation: 98398

Most straightforwardly, just:

update Sandwiches
set Requested=1
where Sandwich in (select distinct Sandwich from Requests)

Upvotes: 0

Related Questions