Delete or Select Records Which Is In Or Not In A View

MySQL

Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:

DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)

An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey

Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.

For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey

Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?

T-SQL

Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:

SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)


http://www.electrictoolbox.com/article/mysql/cross-table-delete/

Quick Sorting Method in SQL Query for Disorderly Sorting Criterion

ORDER BY can be used for orderly sorting criterion only. For disorderly sorting criterion, such as Car, Ship, Airplane, ORDER BY could not accomplish the task. Here is a quick method to use ORDER BY as usually.

Step One: Add order number in front of sorting field by using concatenation function.

Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.

Example in T-SQL:

SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort

This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.