CODEWITHSUNDEEP
phpmysqlsql
Adrian
Adrian

Reputation: 73

SQL/PHP - Join 2 Tables with data from other table

This is the query for SQL statement

UPDATE TrendingEvents
    SET Name = ?, Image = ?, Date = ?, EventCategory = ?
    WHERE ID = ?;')

I would like to access the ID of the other table within my TrendingEvents table. This is the example I've done although it doesn't fully work;

UPDATE TrendingEvents INNER JOIN
       Events AS eID
       ON TrendingEvents.ID = Events.ID
 SET Name = ?, Image = ?, Date = ?, EventCategory = ?
 WHERE eID = ?;')

I would like to update the TrendingEvents table with the ID column from Events table.

The error I'm getting from my statement is 

Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'Name'

Which just shows that I've poorly designed my query.

Upvotes: 0

Views: 108

Answers (2)

GMB
GMB

Reputation: 222462

Both tables seem to contain a column called Name. You need to properly prefix the fields with the table name, like :

UPDATE 
    TrendingEvents AS t
    INNER JOIN Events AS e
        ON t.ID = e.ID 
    SET 
        t.Name = ?, 
        t.Image = ?, 
        t.Date = ?, 
        t.EventCategory = ? 
    WHERE e.eID = ?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You have misused the table alias, so your query should not even compile. Try this:

UPDATE TrendingEvents te INNER JOIN
       Events e
       ON te.ID = e.ID
 SET te.Name = ?,
     te.Image = ?,
     te.Date = ?,
     te.EventCategory = ?
 WHERE e.eID = ?;

Upvotes: 0

Related Questions