Share the Love! Free Programming eBooks

Leave a comment Standard

GoalKicker.com is giving away these free programming books and I thought I would share the love for anyone who hasn’t seen them yet. There are more available from their website but these are the ones I was most interested in. Enjoy!

Screen Shot 2018-01-31 at 4.26.46 PM

Screen Shot 2018-01-31 at 4.26.54 PM

Screen Shot 2018-01-31 at 4.27.01 PM

Screen Shot 2018-01-31 at 4.27.11 PM

Screen Shot 2018-01-31 at 4.27.18 PM

Screen Shot 2018-01-31 at 4.27.24 PM

Screen Shot 2018-01-31 at 4.27.37 PM

Screen Shot 2018-01-31 at 4.27.44 PM

Advertisements

MSSQL Tutorial: Delete All But 1 Duplicate Records

Leave a comment Standard

declare @id int, @total int

/* SELECT COUNT OF POTENTIAL DUPLICATES */
select id, count(id) from [tablename]
where [parameters] group by [column name]

/* LOOP TO CLEAN UP THE DUPLICATES */
declare aff_crsr cursor LOCAL static for
select id, count(id) from [tablename]
where [parameters] group by id

open loop_crsr
fetch from loop_crsr into @id, @total

while @@fetch_status = 0 begin
print cast(@id as varchar) + ‘ – ‘ + cast(@total as varchar) + ‘ – ‘ + cast((@total – 1) as varchar)

delete top (@total-1) from [tablename] where id=@id and [parameters]

fetch from loop_crsr into @id, @total

SQL Tutorial: Select, Update & Delete Syntax

Leave a comment Standard

Select Statements

General Syntax

  • SELECT [column], [column], [column] FROM [tablename] WHERE [column] = [value]

Select All Columns

  • SELECT * FROM [tablename]

Limit # of Records Returned – MSSQL

  • SELECT TOP [number] * FROM [tablename]

Limit # of Records Returned – SQL

  • SELECT * FROM [tablename] LIMIT [# from], [# to]
    • this one works well for pagination. You can select results 0 – 30, then 31 to 60, etc.
  • SELECT * FROM [tablename] LIMIT [number]
    • use this one if you just want to cut off the number of records returned

Sum A Field By Status ID

  • SELECT sum([column]), status_id FROM [tablename] WHERE [column] = [value] GROUP BY status_id

Count A Field

  • SELECT count([column]) FROM [tablename] WHERE [column] = [value]

Sort By A Field Ascending or Descending

  • SELECT [column], [column] FROM [tablename] WHERE [column] = [value] ORDER BY [column] DESC, [column] ASC

Complex Where Values

  • SELECT [column], [column] FROM [tablename] WHERE ([column] = [value] AND [column] = [value]) OR ([column] = [value] AND [column] = [value])

Select From Result Subset

  • SELECT [column], [column] FROM [tablename] WHERE [column] IN ([value], [value], [value])

Select From Select Subset

  • SELECT [column] FROM [tablename] WHERE [column] IN (SELECT [column] FROM [tablename])

Inner Join Select All – records must exist in table B and table A matching on conditions

  • SELECT * FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Left Join Select All – records must exist in table A

  • SELECT * FROM [tablename] A LEFT OUTER JOIN [tablename] B on A.[column] = B.[column] WHERE B.[column] = [value]

Inner Join Select All From One Table, Select Fields From Another

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]

Unions – results and columns must match in both SQL statements

  • SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value] UNION SELECT C.*, D.[column], D.[column] FROM [tablename] C INNER JOIN [tablename] D ON C.[column] = D.column WHERE C.[column] = [value]

Delete Statements

Always do a select statement first to make sure the records you’re about to delete are the ones you want. Also consider any triggers that may be fired as a result of deleting these records.

General Syntax

  • DELETE FROM [tablename] WHERE [column] = [value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Now replace the text in red with the text in blue below to delete the records you found
  • DELETE FROM [tablename] WHERE [column] = [value]

Update Statements

Make sure you do a select statement before attempting to update records to ensure you only update the records you intended. Also consider any triggers that may be fired as a result of updating these records.

General Syntax

  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]

Check Yourself

  • SELECT * FROM [tablename] WHERE [column] = [value]
  • Replace the blue section with what’s in red below, then add the columns and values you want to update as seen in green below
  • UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]

SQL Tutorial: Easily Update Rows Based on Complex Joins

Comment 1 Standard

There are a lot of times I’d like to update hundreds of rows in a database based on a complex set of conditions. In doing a traditional update SQL statement there is no way to pull in joins and compare the values. My little cheat for this is to run a select statement that generates all the appropriate update statements. Then all you have to do is run the resulting SQL string.

For example: Lets say you have a car racing game. You added a new garage feature where members can sort their cars into the appropriate garage. By default you gave each member their own garage when you created the new feature. Now you want to update each car belonging to every member so that it is placed inside the appropriate garage. You could build something like the following.

select ("update cars set garage='" + G.id + "' where car_id='" +  C.id + "';") as SQL from garage G
inner join cars C on C.owner = G.owner

This would give you results along the lines of:

update cars set garage='2' where car_id='3421';
update cars set garage='2341' where car_id='133212';
update cars set garage='4452'' where car_id='98321';

Ten Reasons Not To Use ASP!

Comment 1 Standard

10. I have to write my own isNull function?

9. No double equals = retarded language

8. You can’t response.write across multiple lines unless you _ & fix them. Can’t concatenate across multiple lines unless you _ & fix them.

7. WHERE THE HELL IS ++ or – – or -= or += or all the others? I think they made it so we had to do count = count + 1 just to watch us all suffer and tear our hair out.

6. Comments are apostrophes. So you know, when you’re typing up strings you have extra fun trying to read things like “and he said, “”don”t mess wit me ”sucka”””” ‘isn’t that just funny! you can’t tell where my strings end and my comments begin!

5. No block comments. Enough said. WTF?

if 1=0 then

put my comment crap here if I want to type more than one line or if my comment is so long that I want to put a paragraph return so it doesn’t run outside the 80 line view of my text editor. Pure GENIUS!

end if

4. If you have a table like this:

id –  int
comment – text
active – bool
category – int
date – timestamp

And you pull select * from this table into a dataset then the fields of type text overwrite the values all the fields underneath it: active, category, date, etc all come up blank even though they really have a value.

3. I heart Linux

2. No associative arrays from datasets unless you switch scripts and hack in some Javascript or create a scripting dictionary. Instead I have to loop through the dataset each time, what a waste of resources. Who in their right god-forsaken mind decided this wasn’t a must have? Obviously someone who didn’t do a lot of ASP with databases….

1.  Subjectivity XKCD:

MSSQL Tutorial: Select All Tables & Fields

Leave a comment Standard

SQL: select table_name, column_name, data_type, character_maximum_length, is_nullable from
information_schema.columns where table_name in (select name from sysobjects where xtype=’U’) order by table_name

CHM & PDF Programming Manuals

Comments 3 Standard

I know these have come in handy for me over the years. Right click and save link as.