CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
user7648646
user7648646

Reputation:

SQL Server subqueries error

I need a little help with my query. I am new to subqueries. I want to replace every row in the table that has null values in the last name column with no last name and also output all the values with no Last name. I get an error and I am not sure how to get around this. Will appreciate any help. 

SELECT
    Firstname, 
    ISNULL(dbo.Mechanics.LastName, 'No lastname') AS Lastname
FROM   
    dbo.Mechanics
WHERE  
    Lastname = (SELECT FirstName, Lastname 
                FROM Mechanics 
                WHERE LastName = 'No lastname');

I'm getting this error:

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Upvotes: 0

Views: 87

Answers (6)

Egor Mikhailov
Egor Mikhailov

Reputation: 29

If You want UPDATE table data and view updated records You can output query results using OUTPUT clause: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Query shuld be:

UPDATE Mechanics
SET LastName = 'No lastname' 
FROM Mechanics WHERE LastName IS NULL
OUTPUT inserted.*

If You want to select all data with corrected last name:

SELECT FirstName, ISNULL(LastName, 'No lastname') AS LastName FROM dbo.Mechanics

COALESCE function (multiple arguments) is little bit slowlier then ISNULL function (only 2 args)

Upvotes: 0

Von Abanes
Von Abanes

Reputation: 716

    SELECT  m.Firstname ,
            ISNULL(m.LastName, 'No lastname') AS Lastname
    FROM    dbo.Mechanics M
    WHERE   EXISTS ( SELECT 1
                     FROM   Mechanics M1
                     WHERE  m1.LastName = 'No lastname'
                            AND m.LastName = m1.LastName )

Upvotes: 1

Uzair
Uzair

Reputation: 196

You don't have two write any sub queries as you can UPDATE the table first where the column IS NULL

UPDATE dbo.Mechanics SET LastName ='No Lastname' WHERE LastName IS NULL

and then use the SELECT statement with the WHERE clause to equal the LastName to 'No Lastname'

SELECT FirstName,LastName FROM dbo.Mechanics WHERE LastName = 'No Lastname'

EDIT: According to your comment you don't want to permanently update the LastName column than you can use COALESCE

SELECT FirstName,COALESCE(LastName, 'No lastname') AS LastName FROM dbo.Mechanics WHERE LastName IS NULL

Upvotes: 0

Bryan Reis
Bryan Reis

Reputation: 31

Not sure a subquery is needed here. Try this:

SELECT 
       Firstname, 'No Lastname' AS Lastname
FROM   Dbo.Mechanics
WHERE Lastname IS NULL ;

Upvotes: 1

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

I want to replace every row in the table that has null values in the last name column with no last name

This is done with an UPDATE statement:

UPDATE Dbo.Mechanics SET LastName = 'No lastname' WHERE LastName IS NULL

and also output all the values with no Last name

Then, select is easy:

SELECT FirstName, LastName FROM Dbo.Mechanics WHERE LastName = 'No lastname'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I don't see what a subquery has to do with this. This should be sufficient:

SELECT m.Firstname, COALESCE(m.LastName, 'No lastname') AS Lastname
FROM Dbo.Mechanics m
WHERE m.Lastname IS NULL;

You should also learn how to use table aliases, so your queries are easier to write and to read.

Upvotes: 0

Related Questions