sql insert and update question..multiple queries in one statement

Question

I have a table called auctions, which has various columns such as username, auction id(the primary key), firstname, lastname, location etc, as well as a category column. The category column is blank by default, unless it is filled in for a particular record by a user.

I have made a new users table, which has username and category columns, as well as aditional fields which will be completed by user input.

I would like to know if it is possible when updating a record in the auctions table to have a category, to insert the username and category from that record into the users table as long as the username is not already present in the table.

For example, if I have the following tables:

auctions

auctionid   username    firstname   lastname    category
------------------------------------------------------------------------
1       zerocool    john        henry       
2       fredflint   fred        smith       
3       azazal      mike        cutter

Then, upon updating the second record to have a catagory like so:

2       fredflintsoner  fred        smith       shoes

The resulting users table should be:

users

username    shoes   pants   belts   misc1   misc2
--------------------------------------------------
fredflint   true

With no record have existed previously.

If additional auctions exist with the same username in the auctions table, such as:

7       fredflint   fred        smith   belts

Then even if this auction is added to the category, a new record should not be inserted for the users table, as the username is already , however it should be updated as necessary, resulting in:

username    shoes   pants   belts   misc1   misc2
--------------------------------------------------
fredflint   true        true

Paolo Bergantino · Accepted Answer · 2009-04-13 19:56:26Z

2

What you are looking for is known as a TRIGGER. You can specify something to run after every insert/update in the auctions table and then determine what to do to the users table.

answered Apr 13, 2009 at 19:56

Paolo Bergantino

490k83 gold badges523 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andomar · Accepted Answer · 2009-04-13 20:14:16Z

1

A couple of questions come to mind. The first is, your user table looks denormalized. What happens when you add a new category? Consider a user table in the form of:

id username category

Where you have multiple rows if a user has multiple categories:

1 fredflint shoes
2 fredflint pants
....

The second question I have is, why do you need a user table at all? It looks like all the information in the user table is already stored in the auction table! You can retrieve the user table simply by:

select distinct username, category
from auctions

If you need the separate table, an option to manually update the table when you create a new auction. I'd do it like this (I know just enough about triggers to avoid them):

1 - Make sure there's a row for this user

   if not exists (select * from users where username = 'fredflint')
       insert into users (username) values ('fredflint')

2 - Make sure he the shoe category

   if not exists (select * from users where username = 'fredflint' and shoes = 1)
       update users set shoes = 1 where username = 'fredflint'

answered Apr 13, 2009 at 20:14

Andomar

239k55 gold badges387 silver badges412 bronze badges

5 Comments

user1253538 Over a year ago

I need a users table for additional user input about the users making the auctions. My users table will have columns for each of the categories, not a seperate record for each category

Karl Over a year ago

"My users table will have columns for each of the categories, not a seperate record for each category" - consider the implications of this. Take a look for a way to acheive the same goal with normalised data sets.

user1253538 Over a year ago

what are the implications of this? it is the most efficient and works the best for my needs as I see it...., having mnay records with the same username would cause havoc.

Andomar Over a year ago

In a normalized database you'd store user specific information in a User table, and all categories per user in a UserCategory table. The UserCategory table can then have multiple rows per user.

Karl Over a year ago

What happens if a new category gets added? One gets removed? This would require a partial DB rewrite to make a business change. Google Deletion anomaly, insertion anomaly, etc. I strongly suggest that this desing is not great now and will be an impediment to later enhancements.

Collectives™ on Stack Overflow

sql insert and update question..multiple queries in one statement

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related