EzDevInfo.com

UPDATE from SELECT using SQL Server

In SQL Server, it's possible to insert into a table using a SELECT statement:

INSERT INTO Table (col, col2, col3)
    SELECT col, col2, col3 FROM other_table WHERE sql = 'cool'

Is it also possible to update via a SELECT? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:

UPDATE Table SET col1, col2
    SELECT col1, col2 FROM other_table WHERE sql = 'cool'
    WHERE Table.id = other_table.id

Add a column, with a default value, to an existing table in SQL Server

How can a column with a default value be added to an existing table in SQL Server 2000 / SQL Server 2005?

Insert results of a Stored Procedure into a Temporary Table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select * 
into tmpBusLine
from BusinessLine

Trying the same, but using a stored procedure that returns data, is not quite the same.

select * 
into tmpBusLine 
from
exec getBusinessLineHistory '16 Mar 2009'

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

Finding duplicate values in a SQL table

It's easy to find duplicates with one field:

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

So if we have a table

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

This query will give us John, Sam, Tom, Tom because they all have the same email.

However, what I want is to get duplicates with the same email and name.

That is, I want to get "Tom", "Tom".

The reason I need this: I made a mistake, and allowed to insert duplicate name and email values. Now I need to remove/change the duplicates, so I need to find them first.

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Concatenate many rows into a single text string?

Consider a database table holding names, with three rows:

Peter
Paul
Mary

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Difference between INNER and OUTER joins

What is the difference between INNER JOIN and OUTER JOIN?

How do LEFT JOIN, RIGHT JOIN, and FULL JOIN fit in?

How can I prevent SQL-injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 

mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?

When should I use Cross Apply over Inner Join?

What is the main purpose of using CROSS APPLY?

I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

I also know that CROSS APPLY doesn't require a UDF as the right-table.

In most INNER JOIN queries (one-to-many relationships), I could rewrite them to use CROSS APPLY, but they always give me equivalent execution plans.

Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?

Edit:

Here's a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where cross apply is faster/more efficient)

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.

I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.

This is the Sales_Import table, where the account number field needs to be updated:

LeadID  AccountNumber
147         5807811235
150         5807811326
185         7006100100007267039

And this is the RetrieveAccountNumber table, where I need to update from:

LeadID  AccountNumber
147         7006100100007266957
150         7006100100007267039

I tried the below, but no luck so far:

UPDATE [Sales_Lead].[dbo].[Sales_Import] 
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                RetrieveAccountNumber.LeadID) 

It updates the card numbers to account numbers, but the account numbers gets replaced by NULL

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL.

You can do:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Are those two worked on the same way by MySQL?

Should I use != or <> for not equal in TSQL?

I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?

I like != because <> reminds me of Visual Basic.

How to reset AUTO_INCREMENT in MySQL?

How can I reset the auto-increment of a field? I want it to start counting from 1 again.

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?