INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Script needed

SQL Script needed

(OP)
I need a SQL script for the following:

Select a specific account ID from tblAccounts that has a warranty expiration date of x.

SELECT tblAccounts
WHERE AccountID = '15391' (which is the account ID)

then

SELECT tblCustomerInventory
WHERE WarrantyExpiry = '05/30/12'
and then I need to replace all records with the above warranty expiration to have no value.

Thanks for the help!

RE: SQL Script needed

Then you need an Update statement.

Basically:

CODE

UPDATE tblAccounts SET fielName = x WHERE AccountID = 15391 AND WarrantyExpiry = '05/30/12' 


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: SQL Script needed

(OP)
So a couple of questions regarding this SQL Statement:

This is what was provided:

UPDATE tblAccounts
SET filename = x
WHERE AccountID = 15391
AND WarrantyExpiry = '05/30/12'

1. Where do I specify the other table "tblCustomerInventory" within this SQL statement?
2. What does the X represent? What would the filename be?

Here are the 2 tables and their relationships that I would like to use:

tblAccounts WHERE Account ID = 15391

tblCustomerInventory WHERE WarrantyExpiry = '05/30/12'

Thanks!

RE: SQL Script needed

Ahh, I missed the part about there being a secondary table.

Question:

How are these 2 tables related? You select an account from tblAccounts, what does that give you to find in tblCustomerInventory?



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: SQL Script needed

(OP)
If I understand your question:

I use my CRM program to lookup a customer account.

TABLE = tblAccounts.Account ID = 15391
Note: The AccountID is an extension of tblAccounts.

Then, I click on a button to view the inventory sold to that customer.
TABLE = tblCustomerInventory

Within the Customer Inventory table are additional tables that contain the item#, item description, date sold, and the warranty expiration date.

tblCustomerInventory.WarrantyExpiry

Therefore, I want to remove the WarrantyExpiry date from the tblCustomerInventory for the customer whose tblAccounts.AccountID = '15391'.

Hope that helps clarify what I'm looking for.

Thanks!

RE: SQL Script needed

My question was more geared towards how do you know if a record or row in the table tblCustomerInventory belongs to a specific AccountID?

This is called a relationship. What is the relationship between the tables? Without that there is no way to create the relevant query.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: SQL Script needed

(OP)
Sure - I understand.

Both tables have a similar link - called the AccountNumber.

tblAccounts.AccountNumber

tblCustomerInventory.AccountNumber

Does that help? I can use the account number instead of account ID.

Thanks!

RE: SQL Script needed

You can try something like:

CODE -->

UPDATE `tblCustomerInventory` SET `WarrantyExpiry` = NULL WHERE AccountNumber = (SELECT AccountNumber FROM tblAccounts WHERE AccountID="15391") 


Basically you can use the AccountID to find the relevant AccountNumber for that ID, and then use that to find the relevant records in tblCustomerInventory and set the WarrantyExpiry to null.

The inner Select must return only a single result for the query to work. You can add a Limit to make sure it does return only a single record.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close