Selecting a running position of the returned row in MSSQL

For some reason this concept is hard to summarize in one line. Basically we’re looking to return how many rows are in the table before the row returned in the result. Sort of like what position the rows is in line. If we have a table like this: Row: 1,2,3 Row: 2,4,5 Row: 3,6,7 And we want to find out the position of row 2, then it’s fairly simple because the primary key will tell us what order it is. However, if you delete row 1,...

Read More

MySQL Update Row if Key Exists

Many times you have to do a query before you do a write action to a database because you have to see whether or not a row exists for the data you want to put in. If it does exist, you’d then do another query that was an update, otherwise you’d have to do an insert. Well if you want a quicker way, and you don’t need to use operators like “Update `column`+1″, then “REPLACE INTO” is the function for you. ...

Read More

MySQL Limiting Left Joins

Sometimes it’s nice to just use a left join to see if a column exists. That’s what left joins are for. Your base query will still return rows even if there are no rows in the joined table(s). However, what if there is a one to many relationship between the base table and the joined table, that is to say that for any row in the base table there could be any number of rows in the joined table. If you have more than one row in the...

Read More

Inserting from the results of a MySQL SELECT

If you need a quick way to insert into another table from the results of a query, the best way would be to use the INSERT-SELECT Syntax. It’s quite simple. INSERT INTO tableA (FieldA, FieldB) (SELECT FieldC, FieldD FROM tableB) ; Note that field A & C and B & D should have the same data type. Using this method you can avoid any secondary programming language or loops to achieve your insert.

Read More