Posts Tagged sql syntax
Powerful Data Insertion Features in MySQL
There are several powerful techniques for getting your data into a MySQL database. MySQL supports non-standard extensions to SQL, giving you more flexibility in certain situations. If you want to take full advantage of MySQL, keep these tips in mind. The Problem Say you need to shove some new data into a table. Naturally, you want to INSERT. But it so happens that if the data is already there, you want to UPDATE the existing data. You don't care if you have to INSERT or UPDATE - you just want the new data in the table. You decide do one of two things. You might try DELETE FROM yourtable WHERE key='yourkey'; INSERT INTO yourtable (key, data1, data2) VALUES ('yourkey', 1, 2); This is simple and effective. You could also check to see if the record already exists, and if so, UPDATE. What you really wish you had is an "INSERT OR UPDATE" command. Allow me ...
On Delete Cascade
ON DELETE CASCADE In a relational database where would you use the following construct? What does it do? … ON DELETE CASCADE Answer ON DELETE CASCADE is used when creating a table that has a field with a FOREIGN KEY. The purpose it to ensure that when a row in the parent table is deleted all of the related rows in the CHILD table are deleted. Example CREATE TABLE cities ( city varchar(80) primary key ); CREATE TABLE weather_history ( city varchar(80) references cities(city) ON DELETE CASCADE, temp_lo int, temp_hi int, date date ); The parent table is cities, the child is weather_history. We ...