Update with a join?

Hello I am trying todo the following:

UPDATE Post_Gallery post_id
SET pg.post_id = c.post_id
FROM _Content AS c
WHERE c.id = pg.id

However I get an error at ‘FROM _Content…’

Any help is much appreciated



[[COLOR=#990099][B]UPDATE[/B][/COLOR] Post_Gallery, Content [URL="http://search.mysql.com/search?site=refman-%35%31&q=SET"][COLOR=#990099][B]SET[/B][/COLOR] Post_Gallery.post_id [COLOR=#CC0099]=[/COLOR] Content.post_id  [URL="http://search.mysql.com/search?site=refman-%35%31&q=WHERE"][COLOR=#990099][B]WHERE[/B][/COLOR]](http://search.mysql.com/search?site=refman-%35%31&q=UPDATE) Content.id [COLOR=#CC0099]=[/COLOR] Post_Gallery.id

Amazing, thank you

or like that

UPDATE p
SET p.post_id = c.post_id
FROM Post_Gallery p join _Content AS c
on p.id=c.id

no, not like that – did you see post #1? the use of FROM is invalid

It is work…

you have

UPDATE Post_Gallery post_id
SET pg.post_id = c.post_id
FROM _Content AS c
WHERE c.id = pg.id

and in from you must join both tables this is where the problem

update tableWhichNeedsUpdate
set <value=value>
from table1 join table2
on table.field=table2.field
where <condition>

this is a schema

Seems to be a MySQL versus MS SQL situation again.

From the MySQL page, UPDATE doesn’t allow FROM
However, MS SQL does.

This is why I wish sitepoint hadn’t merged mysql in with the other databases forum when they “tidied” up the forums. Either that or everyone should preface their question with choice of database app in a pull-down menu that is a required field before posting.

SIGH

but if one reads post #1 carefully, one notices that the error message pointed to the FROM keyword as causing the error

ergo… :smiley:

Off Topic:

I have shared your suggestion with the Staff

Yes, but if you read it without making assumptions, it purely states he receives an error on the line FROM _Content, doesn’t specify what the error is, just the line it is thrown on (for all we know it could be a syntax issue – since the query was obviously broken when posted). :smiley: Nonetheless, I agree that the consolidation can at times make it harder to quickly resolve a problem if you don’t know what Database the user is running.

so what’s wrong with asking, when necessary?

many query problems can be resolved with sql that is so “vanilla” that it will work in any database

UPDATE is ~not~ in that category

neither are date functions, etc.

so if it matters, and the original poster hasn’t declared which database system is being used, i always ask first

:slight_smile:

Off Topic:

I agree, you can always ask, but sometimes being proactive to a problem that is an outcome of consolidation might be a better approach. Asking does typically solve the problem, but I’d like to explore ways of not having to do that (if one exists). Also, I can think of MANY differences just between MySQL and SQL Server, let alone when you add Oracle or Postgress to the mix.