TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. delete select exists query example

 Delete data form jos_comprofiler where not related data from table jos_users; fields jos_comprofiler.user_id= jos_users.id:
DELETE
FROM jos_comprofiler
WHERE NOT EXISTS (

SELECT *
FROM jos_users
WHERE jos_comprofiler.user_id = jos_users.id
)

Remove dublicate rows
DELETE
FROM t_quotes_tmp
USING t_quotes_tmp, t_quotes_tmpAS vtable
WHERE vtable.id_r > t_quotes_tmp.id_r
AND t_quotes_tmp..text_en = vtable..text_en

At first better test:

SELECT *
FROM t_quotes_tmp, t_quotes_tmp AS vtable
WHERE vtable.id_r > t_quotes_tmp.id_r
AND vtable.text_en = t_quotes_tmp.text_en
LIMIT 0 , 30

Another Example:
DELETE from t_songs where t_songs.id_r
IN (SELECT t_songs_tmp.id_r
FROM t_songs_tmp, t_songs_tmp AS vtable
WHERE vtable.id_r > t_songs_tmp.id_r
AND trim(vtable.name) = trim(t_songs_tmp.name) and vtable.author = t_songs_tmp.author)
 
Previous articlePage topNext article  ALL TOPICS



 Use username: Guest, Anonymous, Programmer






QUOTES:
I regret that I have but one life to give for my country.
Nathan Hale
Children enjoy the present because they have neither a past nor a future.
Jean De La Bruyere
Pain and foolishness lead to great bliss and complete knowledge, for Eternal Wisdom created nothing under the sun in vain.
Kahlil Gibran