Update data in one table with data from another table

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)

TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I.    TableA

a    b    c    d

1    x    y    z

2    a    b    c

3    t    x    z

II.    TableB

a1    b1    c1    d1    e1

1    x1    y1    z1    40

2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

/

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

SQL Server:

UPDATE TABLEA

SET     b = TABLEB.b1,

c = TABLEB.c1,

d = TABLEB.d1

FROM TABLEA, TABLEB

WHERE TABLEA.a = TABLEB.a1

AND TABLEB.e1 > 40

GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

DB2 LUW:

–Same as Oracle–

UPDATE TABLEA

SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)

WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Results after the update:

a    b    c    d

————————————

1     x          y           z

2     a1        b1         c1

3     t           x           z

NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon.  If it is incorrect, then you can get wrong results.  The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:

a    b    c    d

————————————

1     Null      Null      Null

2     a1        b1         c1

3     Null     Null      Null

Maintain Scrollbar Position Inside UpdatePanel After Partial PostBack

Do you want to maintain the scroll position of a GridView, Div, Panel, or whatever that is inside of an UpdatePanel after an asynchronous postback? Normally, if the updatepanel posts back, the item will scroll back to the top because it has been reloaded. What you need to do is “remember” where the item was scrolled to and jump back to there after the postback. Place the following script after the ScriptManager on your page. And since the _endRequest event of the PageRequestManager happens before the page is rendered, you’ll never even see your item move!

Code Screen Shot below :



Actual Code below :

<script type="text/javascript">

var xPos,yPos;
var prm = Sys.WebForms.PageRequestManager.getInstance();

function BeginRequestHandler(sender,args){
 if($get('<%= Panel1.ClientID %>') != null){
  xPos = $get('<%= Panel1.ClientID %>').scrollLeft;
  yPos = $get('<%= Panel1.ClientID %>').scrollTop;
 }
}

function EndRequestHandler(sender,args){
 if($get('<%= Panel1.ClientID %>') != null){
  $get('<%= Panel1.ClientID %>').scrollLeft = xPos;
  $get('<%= Panel1.ClientID %>').scrollTop = yPos;
 }
}

prm.add_beginRequest(BeginRequestHandler);
prm.add_endRequest(EndRequestHandler);
</script>


<form runat="server">
 <asp:ScriptManager id="ScriptManager1" runat="server" ScriptMode="Release" />
 <asp:UpdatePanel ID="UpdatePanel1" runat="server">
  <ContentTemplate>
    <asp:Panel ID="Panel1" runat="server" >
       <pre>
         <asp:Literal ID="litXML" runat="server" />
       </pre>                    
    </asp:Panel>
  </ContentTemplate>
  </asp:UpdatePanel>
</form>

Backup and Restore From Sql to Local /Network Machine

Backup/Restore for local machine....

Declare @dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation varchar(Max)
Set @filelocation = 'D:/'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

Backup/Restore for Network machine....

Declare @dbName varchar(100)
Set @dbName = 'dbMurli'
Declare @filelocation nvarchar(Max)
Set @filelocation = N'\\192.0.0.72\Share\db\'+@dbName+'.bak';
BACKUP DATABASE @dbName TO DISK = @filelocation
Restore DATABASE @dbName FROM DISK = @filelocation

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command.

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli.bak'
WITH REPLACE

Is Exists in SQL

For database check

if db_id('dbname') is not null

For table check

if object_id('object_name', 'U') is not null -- for table

For Procedure existing

if object_id('object_name', 'P') is not null -- for SP

Simple script to Restore all SQL Server databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
--BACKUP DATABASE @name TO DISK = @fileName
RESTORE DATABASE @name FROM DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

If you Found

Error 3154: The backup set holds a backup of a database other than the existing database.

Solution is very simple and not as difficult as he was thinking. He was trying to restore the database on another existing active database.

Fix/WorkAround/Solution:

1) Use WITH REPLACE while using the RESTORE command. View Example

2) Delete the older database which is conflicting and restore again using RESTORE command.

I understand my solution is little different than BOL but I use it to fix my database issue successfully.

3) Sample Example :
RESTORE DATABASE 'dbMurli'
FROM DISK = D:\dbMurli.bak'
WITH REPLACE

Find Specific String or Char Count occur in a string

For getting The String count I have created three methods might be this will helpful to you.

in SQl server, we dont have any function which can return the number of words in a perticular string .but from small tricks we can do that . now Consider here we can separating the character on space basis

example :


DECLARE @String VARCHAR(4000)

SET @String = 'WelCome to D Murli Blog Spot.'

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1

Above query will return value 6 , but if the words are separate by more than one space then it will aslo count that space. but its wrong as per the answer.
in that case Create one function which can keep multiple spaces as a single space and return proper result

Below is a Function which can remove white space and all and retrun peoper result.

CREATE FUNCTION [dbo].[WordCount] ( @inStr VARCHAR(4000) )

RETURNS INT

AS

BEGIN

DECLARE @Index INT

DECLARE @Char CHAR(1)

DECLARE @PrevChar CHAR(1)

DECLARE @WordCount INT

SET @Index = 1

SET @WordCount = 0

WHILE @Index <= LEN(@InStr)

BEGIN

SET @Char = SUBSTRING(@InStr, @Index, 1)

SET @PrevChar = CASE WHEN @Index = 1 THEN ' '

ELSE SUBSTRING(@InStr, @Index - 1, 1)

END

IF @PrevChar = ' ' AND @Char != ' '

SET @WordCount = @WordCount + 1

SET @Index = @Index + 1

END

RETURN @WordCount

END

GO

This is third method that will first split the string and count the string bases on loop

DECLARE @NextString NVARCHAR(40)

DECLARE @Pos INT

DECLARE @NextPos INT

DECLARE @String NVARCHAR(40)

DECLARE @Delimiter NVARCHAR(40)

DECLARE @Counter int

Set @Counter = 0

SET @String ='18,21,22'

SET @Delimiter = ','

SET @String = @String + @Delimiter

SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)

BEGIN

SET @NextString = substring(@String,1,@Pos - 1)

--SELECT @NextString -- Show Results

SET @String = substring(@String,@pos+1,len(@String))

SET @pos = charindex(@Delimiter,@String)

Set @Counter = @Counter + 1

END

Get Reverse String in XSL

  <xsl:template name="reverse">
    <xsl:param name="theString"/>
    <xsl:variable name="thisLength" select="string-length($theString)"/>
    <xsl:choose>
      <xsl:when test="$thisLength = 1">
        <xsl:value-of select="$theString"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:variable name="restReverse">
          <xsl:call-template name="reverse">
            <xsl:with-param name="theString"
            select="substring($theString, 1, $thisLength -1)"/>
          </xsl:call-template>
        </xsl:variable>
        <xsl:value-of
        select="concat(substring($theString,$thisLength, 1) ,$restReverse)"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!--*****************Start Get File Name from URL *****************-->
  <xsl:template name="GetFileName">
    <xsl:param name="inputString"/>
    <xsl:variable name="delimiter">/</xsl:variable>
    <xsl:variable name="xxx">
      <xsl:call-template name="reverse">
        <xsl:with-param name="theString" select="$inputString" />
      </xsl:call-template>
    </xsl:variable>

    <xsl:variable name="zzz">
      <xsl:call-template name="reverse">
        <xsl:with-param name="theString" select="substring-before($xxx,$delimiter)" />
      </xsl:call-template>
    </xsl:variable>

    <xsl:value-of select="substring-before($zzz,'.')"/>
  </xsl:template>
  <!--*****************End Get File Name from URL *****************-->