RoadRunner RoadRunner - 2 months ago 6
SQL Question

Change table Information

I have three tables:

Table 1: (Consumer)

UserName | FirstName | LastName
'Magika12' 'Ronald' 'Ludwig'


Table 2: (ConsumerLocation)

UserName | LocationID
'Magika12' 13234


Table 3: (Location)

LocationID | StreetNumber | StreetName | Suburb | City | Postalcode
13234 13 Baker Street Melton Brisbane 4242


And I would like to change the address of
Magika12
to this instead:

"124 Braelands Crescent, Albion, Melbourne, 9999"


Whereby the new table would look like:

LocationID | StreetNumber | StreetName | Suburb | City | Postal code
13234 124 Braelands Crescent Albion Melbourne 9999


I have tried something like this:

UPDATE
L1
SET
L1.StreetNumber = 124,
L1.StreetName = 'Braelands Crescent',
L1.Suburb = 'Albion' ,
L1.City = 'Melbourne',
L1.Postalcode = 9999
FROM Location L1
INNER JOIN ConsumerLocation
WHERE ConsumerLocation.UserName = 'Magika'


I know this is not correct, But I am not sure how to connect all the tables together to update the address of
Magika12
. I have made
UserName
the primary key of
Consumer
, and the primary foreign key of
ConsumerLocation
. And
LocationId
the primary key of
Location
and the primary foreign key of
Consumer Location
.

Any help would be appreciated

Dez Dez
Answer
UPDATE 
    Location L1 
    JOIN ConsumerLocation CL ON CL.LocationID = L1.LocationID
SET 
    L1.StreetNumber = 124,
    L1.StreetName = 'Braelands Crescent',
    L1.Suburb = 'Albion' ,
    L1.City = 'Melbourne',
    L1.Postalcode = 9999
WHERE CL.UserName = 'Magika12';

In case you have multiple Location entries related to same username add to WHERE clause the LocationId:

WHERE CL.UserName = 'Magika12' AND CL.LocationID = 13234