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?
CodePudding user response：
Most straightforwardly, just:
update Sandwiches
set Requested=1
where Sandwich in (select distinct Sandwich from Requests)
CodePudding user response：
Update SANDWICHES JOIN REQUESTS ON SANDWICHES.SANDWICH = REQUESTS.SANDWICH set SANDWICHES.REQUESTED = 1 WHERE SANDWICHES.REQUESTED = NULL