Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ajaypal
Starting Member
6 Posts |
Posted - 2013-07-22 : 15:49:19
|
Hi ,I have a extremely large table and out of that I just need 2 records but based on some logic . For example IF EMPLOYEE_ADDRESS <> NULL set Employee_HAS_HOME to 1ELSE set Employee_HAS_HOME to 0here I can not use just first 1000 records as I am not sure If I will cover the second condition in first 1000 rows . Then I came up with solution like : - select * from employee_table where employee_address <> NULL LIMIT 1UNION select * from employee_table where employee_address = NULL LIMIT 1And this will give me 2 records which I can use for my testing .But unfortunately this is not working. Could you please suggest me some other efficient way or tell me what is wrong in the query |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 15:55:14
|
UPDATE dbo.Table1 SET Employee_Has_Home = CASE WHEN Employee_Address IS NULL THEN 0 ELSE 1 END Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
ajaypal
Starting Member
6 Posts |
Posted - 2013-07-22 : 16:02:31
|
Slight Change , there is a existing table and the condition is on this existing table. Based on the result I have to check the data in the new table (data already exists but for me to test if data has been migrated successfully , I have to check both the cases) : - IF existing_Table.EMPLOYEE_ADDRESS <> NULLset NEW_Table.Employee_HAS_HOME to 1ELSE set NEW_Table.Employee_HAS_HOME to 0 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 16:06:29
|
[code]UPDATE newtableSET newtable.Employee_Has_Home = CASE WHEN oldtable.Employee_Address IS NULL THEN 0 ELSE 1 ENDFROM dbo.Table1 AS newtableINNER JOIN dbo.Table2 AS oldtable ON oldtable.EmployeeID = newtable.EmployeeID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-23 : 01:59:10
|
quote: Originally posted by ajaypal Slight Change , there is a existing table and the condition is on this existing table. Based on the result I have to check the data in the new table (data already exists but for me to test if data has been migrated successfully , I have to check both the cases) : - IF existing_Table.EMPLOYEE_ADDRESS <> NULLset NEW_Table.Employee_HAS_HOME to 1ELSE set NEW_Table.Employee_HAS_HOME to 0
Use earlier provided suggestionOn a sidenote, please be aware that you cant use =,<> etc opertors with NULL values. to check for NULLs use IS NULL and IS NOT NULL conditions as in previous suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 03:13:49
|
Visakh, please be restrained with that advice.We don't know what ANSI_NULL setting the person has. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-23 : 03:55:51
|
quote: Originally posted by SwePeso Visakh, please be restrained with that advice.We don't know what ANSI_NULL setting the person has. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I think OP's on MySQL (see usage of LIMIT)In MySQL, you cannot turn off ANSI_NULLS settings So OP has to use IS NULL and IS NOT NULL always------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|