CODEWITHSUNDEEP
mysql
İsmail ÇİLOĞLU
İsmail ÇİLOĞLU

Reputation: 67

Mysql delete Inner join not worked?

I want delete data from contact,contact_message and contact_reply data with

contact table (1 data)
-id
-code
-ip
-date
List table

Array
    (
        [0] => Array
            (
                [id] => 1
                [code] => asq12d134
                [mail] => info@localhost
                [date] => 2017-01-18
            )
        [1] => Array
            (
                [id] => 2
                [code] => 1qeacetq
                [mail] =>info@localhost
                [date] => 2017-01-18
            )
                [2] => Array
            (
                [id] => 3
                [code] => awq12sq
                [mail] =>info@localhost
                [date] => 2017-01-18
            )
    )



contact_message table (1 data)
-id
-messageid
-message
-date

list contact_message data 

Array
(
    [0] => Array
        (
            [id] => 5
            [messageid] => 1
            [message] => A new e-mail 
            [date] => 2017-01-18
        )

)


sql query

     DELETE i,im FROM contact AS i
 INNER JOIN contact_message AS im WHERE i.id=im.id  and i.id=1


Result (Error)

    Çözümleme sırasında 3 hata bulundu.

    Beklenmedik belirteç. (near "i" at position 7)
    Beklenmedik belirteç. (near "," at position 9)
    Beklenmedik belirteç. (near "im" at position 11)
    SQL sorgusu:

   DELETE i,im FROM contact AS i
     INNER JOIN contact_message AS im WHERE i.id=im.id  and i.id=1


    MySQL çıktısı: Belgeler

    #1054 - Unknown column 'i.id' in 'where clause'

What is your solution?

Upvotes: 1

Views: 108

Answers (1)

Daniel W.
Daniel W.

Reputation: 32280

I am not sure why it doesn't work but you should (although it's optional) utilize ON instead of WHERE - it reduces the rows to search through drastically and runs faster and safer.

DELETE i, im FROM contact i
JOIN contact_message im ON i.id = im.id 
WHERE i.id = 1

Upvotes: 2

Related Questions