database dansk mysql sql update sql query sql delete insert into sql insert into

Just a quick one about sql - in it's simplest forms - insert into, update and delete.

A friend of mine asked for this, so here goes.

Here's the database set up i will be using for this example:

• 
  Table name:
  
  
  
  • shopping_cart
    
  
  
  
  


• 
  Fields:
  
  
  
  • 
    order_id
    
  
  
  • 
    item
    
  
  
  • 
    quantity
    
  
  
  • 
    price
    
  
  
  • 
    year
    
  
  
  • 
    color
    
  
  
  
  

How's sql insert into work - and what's it do?

What insert into does is simple. It inserts a new record in a database via sql (structured query language). And it works by blotting in the table name, then the fields you want to update and finally the values for these fields. An example would be:


The above statement could be used in a shopping cart (a bit unrealistic to sell a Supra on the net, but still ) - by saying you want to put it into the table called "shopping_cart" (insert into shopping_cart) and then telling what fields you want to have filled out in the database in paranthesis ( - (item, quantity, price, year, color) - ). Finally, the values come into play in another paranthesis but with the word 'values' in front of it. ( - values ('Toyota', 'Supra', '1', '23,000', '1998', 'red') - ).

Sql update - if i had a typo, how do i update it?

You update an sql database via the update command. By telling it which table to update, then which fields and values you want to update. This is however done a little bit differently then the syntax in insert into.

I'll give you an example first, and tell my experiences with the syntax between insert into and update afterwards.



Now your update the table "shopping_cart" (update shopping_cart) and setting the quantity to 2 (quantity='2'), the color to blue (color='blue') and finally the price to 46,000 (price='46,000'), på den ordre som har id 101 (where order_id='101').

The important thing here is that you can update one field up to as many as the table allows and has in it. It's very important in update queries to remember to have a where statement. Without it, you could end up updating all the rows in the table at once, instead of just the one you wanted. With a big database with lots of information, this would probably mean chaos :p.

Would be kind of funny to see Youtube for example try to update a movie title and accidently rename all of them to the same title. I'd presume they have backups and what not, so the issue's not that crucial but youtube wouldn't run to well for some hours as it's get resloved I am sure. But, lets just keep the fun to our thoughts, i doubt it'd be very funny for the people who program it if that happened.

On to my story. I have programmed MySql for alot of years now. Recently, i got a job working for a company called Webex. They use Access databases instead. I've always in MySql used the same syntax for insert into and update, without a problem. I just switched out update with insert into and you could use almost the same query you'd made for both insert into and update. Pretty neat. However, with Access you cannot do that. It only accepts the original way - with the paranthesis and values as explained above.. pretty weird.

Now if you can the other two, sql delete is very simple

Delete works alot like update, only the fields and values are not needed. Your not deleting content, your deleting the whole row from the table.

You do this as so:


Simply: you delete from shopping_cart (delete from shopping_cart) and then you tell it which one you want to remove (where order_id='101').

The only thing here is to say you can delete with whatever which statement you like comparing whatever field and value you want.

Extra tip

In MySql when you update or delete, it's a great idea whenever you want to delete or update a single row to end each query with:

so the statement from above for deletion would be:


This same procedure is done differently in access and would look like:

In Access top 1 before your table is the same as limit 1 in MySql at the end of the query.

Well, I think that ought to help out my friend.

Feel free to ask questions below as always