Updating table with joining id in MySQL

Question

I’m trying to update a table that is joined with another one to update the right record. Here is my command so far:

UPDATE
  links l
SET
  l.l_id = `[value-1]`,
  l.l_facebook = `[value-2]`,
  l.l_youtube = `[value-3]`,
  l.l_twitter = `[value-4]`,
  l.l_googleplus = `[value-5]`,
  l.l_rss = `[value-6]`,
  l.l_homepage = `[value-7]`,
  l.l_freigegeben = `[value-8]`
JOIN
  sponsering ON l.l_id = sponsering.links_l_id
WHERE
  sponsering.s_userID = 2

Trying to run the command in phpmyadmin gives me the following error message:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JOIN sponsering ON l.l_id = sponsering.links_l_id WHERE
sponsering.s_user' at line 12

I need to join the table sponsering because this gives me the correct record in the links table. How can I solve this?

Possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — DaOgre, Oct 24 '15 at 23:00
I don't know the answer for this but by l_faceook you probably mean l_facebook ? — Kostas Mitsarakis, Oct 24 '15 at 23:03

Axalix · Accepted Answer · 2015-10-24T23:17:02.320

1

Try this

UPDATE
  links l,
  sponsering s
SET
  l.l_faceook = `[value-2]`,
  l.l_youtube = `[value-3]`,
  l.l_twitter = `[value-4]`,
  l.l_googleplus = `[value-5]`,
  l.l_rss = `[value-6]`,
  l.l_homepage = `[value-7]`,
  l.l_freigegeben = `[value-8]`
WHERE
  l.l_id = s.links_l_id AND
  sponsering.s_userID = 2

edited Oct 24 '15 at 23:17

answered Oct 24 '15 at 22:59

Axalix

2,831
1
20
37

gives me the following message: 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`webuser462_dev`.`sponsering`, CONSTRAINT `fk_sponsering_links1` FOREIGN KEY (`links_l_id`) REFERENCES `links` (`l_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) – Hack4Life Oct 24 '15 at 23:02
Don't set `l.l_id = [value-1]`. I changed my query. The problem is in records pointing to this `id` from another table(s). So you can't touch them as it can violate the consistency. – Axalix Oct 24 '15 at 23:16
1

Great man! I simply forget that I never wanted to change the `l_id` ... sorry about that and thanks for your fast answer! Approved. – Hack4Life Oct 24 '15 at 23:29

score 0 · Answer 2 · answered Oct 24 '15 at 23:02

0

In MySQL, the join is part of the update statement itself. There is no separate from:

UPDATE links l JOIN
       sponsering s
       ON l.l_id = s.links_l_id
    SET
      l.l_id = `[value-1]`,
      l.l_faceook = `[value-2]`,
      l.l_youtube = `[value-3]`,
      l.l_twitter = `[value-4]`,
      l.l_googleplus = `[value-5]`,
      l.l_rss = `[value-6]`,
      l.l_homepage = `[value-7]`,
      l.l_freigegeben = `[value-8]`
    WHERE s.s_userID = 2;

Some other databases use a FROM clause for the same purpose.

answered Oct 24 '15 at 23:02

Gordon Linoff

1,242,037
58
646
786

give me the following error message: 1451 - Cannot delete or update a parent row: a foreign key constraint fails (webuser462_dev.sponsering, CONSTRAINT fk_sponsering_links1 FOREIGN KEY (links_l_id) REFERENCES links (l_id) ON DELETE NO ACTION ON UPDATE NO ACTION) – Hack4Life Oct 24 '15 at 23:04
@Hack4Life . . . That would be an entirely different problem from the syntax error, because that error occurs when it is running. You need to be sure that the data is correct, and it seems like your `update` is violating a foreign key constraint. – Gordon Linoff Oct 24 '15 at 23:09

Updating table with joining id in MySQL

2 Answers2