更改表信息

Question

我有三张桌子：

表1：(消费者)

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

表2：(ConsumerLocation)

UserName |  LocationID
'Magika12'  13234

表3：(地点)

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

我想把Magika12的地址改为：

"124 Braelands Crescent, Albion, Melbourne, 9999"

新表如下所示：

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

我试过这样的方法：

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'

我知道这是不正确的，但我不知道如何将所有的表连接在一起来更新Magika12的地址。我使UserName成为Consumer的主键，ConsumerLocation的主外键。LocationId是Location的主键和Consumer Location的主外键。

如能提供任何帮助，将不胜感激。

Answer 1

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';

如果您有多个与相同用户名相关的位置条目，添加到WHERE子句中，则LocationId：

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

Answer 2

您错过了join语句的条件。应该是这样的：

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

Answer 3

你的加入需要一个ON条件..。尝尝这个。

 INNER JOIN ConsumerLocation ON Location.LocationId = ConsumerLocation.LocationId

当您在没有任何JOIN条件的情况下进行ON时，您可以在您加入的表中获得所有可能的行组合。可能会有很多排。