RSS

Category Archives: SQL

ALL About SQL

update table with male to female And female to male OR Swap Value of Column Without Case Statement

Just for clarity I am involving the original problem statement here.

USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, 'female'
UNION ALL
SELECT 2, 'male'
UNION ALL
SELECT 3, 'male'
GO
SELECT *
FROM SimpleTable
GO
-- Insert Your Solutions here
-- Swap value of Column Gender
SELECT *
FROM SimpleTable
GO
DROP TABLE SimpleTable
GO

Solution No 1.

UPDATE S
SET S.Gender = D.Gender
FROM SimpleTable S
INNER JOIN SimpleTable D
ON S.Gender != D.Gender

Solution No 2.

CREATE TABLE #temp(id INT, datacolumn CHAR(4))
INSERT INTO #temp
VALUES(1,'gent'),(2,'lady'),(3,'lady')
DECLARE @value1 CHAR(4), @value2 CHAR(4)
SET @value1 = 'lady'
SET @value2 = 'gent'
UPDATE #temp
SET datacolumn = REPLACE(@value1 + @value2,datacolumn,'')

Solution No 3.

UPDATE SimpleTable
SET Gender = RIGHT(('fe'+Gender), DIFFERENCE((Gender),SOUNDEX(Gender))*2)

Solution No 4.

UPDATE St
SET St.Gender = t.Gender
FROM SimpleTable St
CROSS Apply (SELECT DISTINCT gender FROM SimpleTable
WHERE St.Gender != Gender) t

Solution No 5.

UPDATE SimpleTable
SET Gender=X.NewGender
FROM (VALUES('male','female'),('female','male')) AS X(OldGender,NewGender)
WHERE SimpleTable.Gender=X.OldGender

I hope this helps, good luck!

Advertisements
 
Leave a comment

Posted by on October 28, 2013 in Query Logics, SQL

 

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31

 

SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

 
Leave a comment

Posted by on July 6, 2013 in SQL

 

Swap Value Between Two Row Of Columns In MS-SQL

Swap Value Between Two Row Of Columns In MS-SQL

Following Code Will Help you to Swap Value Between Two Row Of Columns In MS-SQL

 begin tran

declare @sum int

select @sum = sum(DisplaySeq)
from CustomCatalogForm
where CustomCatalogFormId in (1,2)

update CustomCatalogForm
set DisplaySeq = @sum - DisplaySeq
where CustomCatalogFormId in (1,2)

commit tran

I hope this helps, good luck!

 

Tags: , , ,

Update data from one existing row to another existing row

Update data from one existing row to another existing row

review the following example

CREATE TABLE #abc(
id int,
Name varchar(50),
SurName varchar(50)
)
Insert into #abc values (1,'Anil','Shah')
Insert into #abc values (2,'Kuldeep','singh')
Insert into #abc values (3,'Waheed','panjri')
Insert into #abc values (4,'Sumit','sutar')

Select * From #abc

Update #abc Set Name= c.Name,
SurName=c.SurName From
(Select Name,SurName From #abc Where id=1)c
Where id=4

I hope this helps, good luck!

 
Leave a comment

Posted by on June 12, 2013 in SQL

 

Tags: , ,

SQL Server – How to Find Total Number of Days in a Month

To Find out Total Number Of day in any month in Sql Server we have to write these Query :
Select datediff(day, GetDate(), dateadd(month, 1, GetDate()))
By using above query we can get total number of days for particular month.
 
 

Differences between Stored Procedures and Functions

  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input/output parameters for it whereas functions can have only input parameters.
  • Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  • We can go for transaction management in procedure whereas we can’t go in function.
  • Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
  • UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
  • UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
  • Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
 
2 Comments

Posted by on April 5, 2013 in SQL