CODEWITHSUNDEEP
mysqlsql
user6064424
user6064424

Reputation:

What is causing "Not unique table/alias" error in mysql?

I have this mysql query below.

UPDATE data_records
  INNER JOIN doors
  ON (sensors.gateway_id = doors.id)
  INNER JOIN data_records
  ON (data_records.sensor_id = sensors.id)
SET data_records.value=1
WHERE doors.addr='FFA'

I am at a loss what caused the error Lookup Error - MySQL Database Error: Not unique table/alias: 'data_records'. I clearly have data_records table in my database.

Upvotes: 1

Views: 68

Answers (3)

sagi
sagi

Reputation: 40481

You either did a mistake, and used data_records twice instead of sensors table, or you just meant to alias the second data_records as sensors . Either way this is wrong .

First option:

UPDATE data_records
  INNER JOIN doors
  ON (sensors.gateway_id = doors.id)
  INNER JOIN sensors
  ON (data_records.sensor_id = sensors.id)
SET data_records.value=1
WHERE doors.addr='FFA'

Second one:

UPDATE data_records
  INNER JOIN doors
  ON (sensors.gateway_id = doors.id)
  INNER JOIN data_records sensors
  ON (data_records.sensor_id = sensors.id)
SET data_records.value=1
WHERE doors.addr='FFA'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You are not join sensors but two time data_recods 

UPDATE data_records
INNER JOIN doors   ON (sensors.gateway_id = doors.id)
INNER JOIN sensors ON (data_records.sensor_id = sensors.id)
SET data_records.value=1
WHERE doors.addr='FFA'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You have two joins to data_records. I think you intend:

UPDATE data_records dr INNER JOIN
       sensors s
       ON dr.sensor_id = s.id INNER JOIN
       doors d
       ON s.gateway_id = d.id
    SET dr.value = 1
WHERE d.addr = 'FFA';

Your query references sensors but does not define it in the UPDATE clause.

Also note that I added table aliases so the query is easier to write and to read.

Upvotes: 4

Related Questions