SQL Script needed

[davismar]

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!

 

[vacunita]

Then you need an Update statement.

Basically:

[COLOR=#3465A4][b]UPDATE[/b][/color] [COLOR=#EF2929][b]tblAccounts[/b][/color] [COLOR=#3465A4][b]SET[/b][/color] [COLOR=#EF2929][b]fielName[/b][/color] = [COLOR=#4E9A06][b]x[/b][/color] [COLOR=#3465A4][b]WHERE[/b][/color] [COLOR=#EF2929][b]AccountID[/b][/color] = [COLOR=#4E9A06][b]15391[/b][/color] [COLOR=#3465A4][b]AND[/b][/color] [COLOR=#EF2929][b]WarrantyExpiry[/b][/color] = [COLOR=#4E9A06][b]'05/30/12'[/b][/color]

----------------------------------
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

 

[davismar]

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!

 

[vacunita]

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

 

[davismar]

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!

 

[vacunita]

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

 

[davismar]

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!

 

[vacunita]

You can try something like:

[b][COLOR=#3465A4]UPDATE[/color][/b] [COLOR=#FF0000]`tblCustomerInventory`[/color] [b][COLOR=#3465A4]SET[/color][/b] [COLOR=#FF0000]`WarrantyExpiry`[/color] [COLOR=#990000]=[/color] [b][COLOR=#3465A4]NULL[/color][/b] [b][COLOR=#3465A4]WHERE[/color][/b] AccountNumber [COLOR=#990000]=[/color] [COLOR=#990000]([/color][b][COLOR=#3465A4]SELECT[/color][/b] AccountNumber [b][COLOR=#3465A4]FROM[/color][/b] tblAccounts [b][COLOR=#3465A4]WHERE[/color][/b] AccountID[COLOR=#990000]=[/color][COLOR=#008800]"15391"[/color][COLOR=#990000])[/color]

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