44 lines
1.4 KiB
MySQL
44 lines
1.4 KiB
MySQL
|
-- INSERT
|
||
|
|
-- Insert stuff in a tableto
|
||
|
|
|
||
|
|
INSERT Vendors
|
||
|
|
(VendorName, VendorAddress1, VendorCity, VendorState, VendorZipCode, DefaultTermsID, DefaultAccountNo)
|
||
|
|
VALUES
|
||
|
|
('Dollar General', 'Rich People Street', 'Beverly Hills', 'CA', '90210', 1, 301),
|
||
|
|
('Best Buy', NULL, 'Beverly Hills', 'CA', '90210', 2, 301),
|
||
|
|
('Barnes & Noble', DEFAULT, 'Beverly Hills', 'CA', '90210', 3, 301)
|
||
|
|
-- Can also use INSERT INTO, there's no different
|
||
|
|
-- INSERT INTO Vendors VALUES (blah, blah, blah)
|
||
|
|
|
||
|
|
-- returns 301, so that's what the account number is set as above
|
||
|
|
SELECT AccountDescription
|
||
|
|
FROM GLAccounts
|
||
|
|
WHERE AccountDescription LIKE '%college%'
|
||
|
|
|
||
|
|
-- See the inserted rows
|
||
|
|
SELECT TOP 10
|
||
|
|
*
|
||
|
|
FROM Vendors
|
||
|
|
ORDER BY 1 DESC
|
||
|
|
|
||
|
|
|
||
|
|
SELECT VendorID, COUNT(*)
|
||
|
|
FROM Invoices
|
||
|
|
GROUP BY VendorID
|
||
|
|
ORDER BY COUNT(*) DESC
|
||
|
|
|
||
|
|
-- Archive some invoices for VendorID 110
|
||
|
|
INSERT INTO InvoiceArchive
|
||
|
|
(InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate)
|
||
|
|
SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate
|
||
|
|
FROM Invoices
|
||
|
|
WHERE VendorID = 110
|
||
|
|
|
||
|
|
SELECT *
|
||
|
|
FROM InvoiceArchive
|
||
|
|
|
||
|
|
-- Update data in DB
|
||
|
|
-- that VendorName obviously doesn't exist so this query does nothing
|
||
|
|
UPDATE Vendors
|
||
|
|
SET VendorAddress1 = '867 Five-Three-O Nn'
|
||
|
|
WHERE VendorName = 'Got your number'
|