WHERE condition doesn't work..... HELP!!!!

[adosidos]

Hi,

I have got two tables. One with a list of cities
(table_name: City
columns: 2
col_1: id
col_2: City)
the other one with a list of names of movie theatres in several cities
(table_name: Cinemas
columns: 16
col_1: id
col_2: City
col_3...
col_16: CityID)

I'm trying to UPDATE 'Cinemas' with the 'id' from table city.
In order to delete the cities from table 'Cinemas' and join them through an "INNER JOIN" command.

My command worked well on another very similar problem (movie titles had been compared and the ID stored... no problem)
Now the exact command doesn't work anymore.

Here it is:
UPDATE Cinemas JOIN City
SET Cinemas.CityID=City.id
WHERE Cinemas.City=City.City

I also tried
SELECT * FROM Cinemas JOIN City
WHERE Cinemas.City=City.City
just to see if anything is selected. doesn't work either :-(
Nothing is selected.

I renamed the columns because I thought perhaps the problem lies there. Nope.

The tables weren't formatted the same way (Cinemas: -City _ VARCHAR(30)- City: -City _ VARCHAR(250)-) but I reformatted the Cinema table and it still does not work.

In both tables is data and the cities are named the same.

Please help. I have no ideas anymore :-(

 

[rac2]

Im not sure what your SELECT query does but you might rewrite it like so

SELECT * FROM Cinemas JOIN City ON Cinemas.City = City.City

If you get no rows that means that none of the cities in the tables match.

 

[adosidos]

Nothing works

I tried:

UPDATE Cinemas JOIN City
ON (Cinemas.City=City.City)
SET Cinemas.CityID=City.id;

UPDATE Cinemas,City
SET Cinemas.CityID=City.id
WHERE Cinemas.City=City.City;

SELECT Cinemas.City, City.City FROM Cinemas
INNER JOIN City
ON Cinemas.City=City.City

SELECT * FROM Cinemas JOIN City ON Cinemas.City = City.City

It has to be something with my columns because in both tables it shows the same data but obviously it is not the "same".
Both tables show the same names of the cities (e.g. "Frankfurt am Main")

mySQL Server version:
# Server Version: 5.0.22-max

perhaps it's about the format?! I don't know.

 

[rac2]

What do you get from these three queries?

SELECT CHAR_LENGTH('Frankfurt am Main'),
       LENGTH('Frankfurt am Main'),
       'Frankfurt am Main'

SELECT CHAR_LENGTH(City),
       LENGTH(City),
       City
FROM Cinemas
WHERE City = 'Frankfurt am Main'

SELECT CHAR_LENGTH(City),
       LENGTH(City),
       City
FROM City
WHERE City = 'Frankfurt am Main'

 

[adosidos]

The problem seems to be in table "City":

SELECT CHAR_LENGTH( 'Frankfurt am Main' ) , LENGTH( 'Frankfurt am Main' ) ,  'Frankfurt am Main';# Zeilen: 1

SELECT CHAR_LENGTH( City ) , LENGTH( City ) , City
FROM Cinemas
WHERE City = 'Frankfurt am Main';# Zeilen: 4

SELECT CHAR_LENGTH( City ) , LENGTH( City ) , City
FROM City
WHERE City = 'Frankfurt am Main';# MySQL lieferte ein leeres Resultat zurück (d. h. null Zeilen).

 

[adosidos]

I got it

I don't know why but the entries in tbale city weren't ok.
I truncated the tabele and inserted them a second time.
Works just fine.
Thanks for all the great tips. I wouldn't have known this.

 

[rac2]

schon gut.