CODEWITHSUNDEEP
mysql
Steven
Steven

Reputation: 19455

Not able to update table from another table

I just did this a few hours ago, but now I'm not able to repeat my "feat".

This is what I'm trying to do:

UPDATE TABLE sl_adr a, sl_address b
SET a.fk_adrID = b.id
WHERE a.street1 = b.street1
AND a.postal_code = b.postal_code
AND a.city = b.city
AND a.fk_countryID = b.fk_countryID

Running this I get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'UPDATE TABLE sl_adr a, sl_address b
SET a.fk_adrID = b.id
WHERE a.street1 = b.'
at line 1

After googling and looking here , I still can't find out what is wrong.

In Toad I can see that the a us underlined with red. But don't understand why.

Upvotes: 2

Views: 90

Answers (2)

Tom Mac
Tom Mac

Reputation: 9853

Aliasing is absolutely fine in update statements. I think the problem here is that you need to replace UPDATE TABLE with just UPDATE. Like this:

UPDATE sl_adr a, sl_address b
SET a.fk_adrID = b.id
WHERE a.street1 = b.street1
AND a.postal_code = b.postal_code
AND a.city = b.city
AND a.fk_countryID = b.fk_countryID;

Upvotes: 0

Devart
Devart

Reputation: 122042

Your query is almost OK, only TABLE keyword is redundant.

UPDATE
  sl_adr a, sl_address b
SET
  a.fk_adrID = b.id
WHERE
  a.street1 = b.street1
  AND a.postal_code = b.postal_code
  AND a.city = b.city
  AND a.fk_countryID = b.fk_countryID

also, this one is possible -

UPDATE sl_adr a
JOIN sl_address b
  ON a.street1 = b.street1
    AND a.postal_code = b.postal_code
    AND a.city = b.city
    AND a.fk_countryID = b.fk_countryID
SET
  a.fk_adrID = b.id

Upvotes: 3

Related Questions