July 23, 2013 at 3:28 am
Hi,
I have 3 tables TableA, TableB, TableC.
I have to update data in TableA with TableB or TableC
If there is data at least one row in TableB , I have to completely ignore TableC and do a INNER JOIN between TableA & TableB.
If there is no data at all in TableB, Then I have to join with TableC and has to update TableA.
Plz help...
July 23, 2013 at 3:52 am
How about something like this:
IF EXISTS (select 1 from Table B)
BEGIN
query with join to table B
END
ELSE
BEGIN
query with join to table C
END
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 3:56 am
IF EXISTS(SELECT 1 FROM TableB)
update data in TableA with TableB
ELSE
update data in TableA with TableC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2013 at 5:15 am
Thanks for the reply, IF/ELSE is straight forward I know this.
But I want in a single select/Update statement.
July 23, 2013 at 5:20 am
deepkt (7/23/2013)
Thanks for the reply, IF/ELSE is straight forward I know this.But I want in a single select/Update statement.
Why? It will be inelegant and unwieldy, and confusing for whoever inherits the code for maintenance purposes. More importantly, it will not perform well.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2013 at 5:30 am
ChrisM@Work (7/23/2013)
deepkt (7/23/2013)
Thanks for the reply, IF/ELSE is straight forward I know this.But I want in a single select/Update statement.
Why? It will be inelegant and unwieldy, and confusing for whoever inherits the code for maintenance purposes. More importantly, it will not perform well.
Second that. You would have to work with LEFT OUTER JOINS, filtering out unnecessary rows to simulate the INNER JOIN, use CASE statements everywhere.
Clarity in your code is more important than trying to put everything in one single statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 23, 2013 at 6:02 am
UPDATE TableA
SET col = CASE WHEN b.cnt >= 1
THEN coalesce(b.col, a.col)
ELSE coalesce(c.col, a.col)
END,
...
FROM TableA a
LEFT JOIN (SELECT *, COUNT(*) OVER() FROM TableB) AS b ON a.keycol = b.keycol
LEFT JOIN TableC c ON a.keycol = c.keycol
Using IF/ELSE is certainly more painless
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 23, 2013 at 6:06 am
Erland Sommarskog (7/23/2013)
UPDATE TableASET col = CASE WHEN b.cnt >= 1
THEN coalesce(b.col, a.col)
ELSE coalesce(c.col, a.col)
END,
...
FROM TableA a
LEFT JOIN (SELECT *, COUNT(*) OVER() FROM TableB) AS b ON a.keycol = b.keycol
LEFT JOIN TableC c ON a.keycol = c.keycol
Using IF/ELSE is certainly more painless
Quite so. Here are two more methods - but I wouldn't recommend any of them.
DROP TABLE #TableA;DROP TABLE #TableB;DROP TABLE #TableC
CREATE TABLE #TableA (ID_a INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, FK INT, Col1 VARCHAR(20));
CREATE TABLE #TableB (ID_b INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Col1 VARCHAR(20));
CREATE TABLE #TableC (ID_c INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Col1 VARCHAR(20));
INSERT INTO #TableA (FK, Col1) VALUES
(1, 'Row 1 in A'),(1, 'Row 2 in A'),(2, 'Row 3 in A'),(2, 'Row 4 in A'),(3, 'Row 5 in A');
INSERT INTO #TableC (Col1) VALUES
('Row 1 in C'),('Row 2 in C'),('Row 3 in C');
-- simple method
;WITH LookupTable AS (
SELECT [Source] = 'B', ID = ID_b, Col1
FROM #TableB
UNION ALL
SELECT [Source] = 'C', ID = ID_c, Col1
FROM #TableC)
SELECT a.*, l.Col1
FROM #TableA a
CROSS APPLY(SELECT [Source] = MIN([Source]) FROM LookupTable) x
INNER JOIN LookupTable l ON l.ID = a.FK AND l.[Source] = x.[Source];
INSERT INTO #TableB (Col1) VALUES
('Row 1 in B'),('Row 2 in B');
;WITH LookupTable AS (
SELECT [Source] = 'B', ID = ID_b, Col1
FROM #TableB
UNION ALL
SELECT [Source] = 'C', ID = ID_c, Col1
FROM #TableC)
SELECT a.*, l.Col1
FROM #TableA a
CROSS APPLY(SELECT [Source] = MIN([Source]) FROM LookupTable) x
INNER JOIN LookupTable l ON l.ID = a.FK AND l.[Source] = x.[Source];
-- slightly more efficient method:
;WITH LookupTable AS (
SELECT [Source] = 'B', ID = ID_b, Col1
FROM #TableB
UNION ALL
SELECT [Source] = 'C', ID = ID_c, Col1
FROM #TableC)
SELECT a.*, l.Col1
FROM #TableA a
CROSS APPLY(SELECT [Source] = CASE WHEN EXISTS (SELECT 1 FROM #TableB) THEN 'B' ELSE 'C' END) x
INNER JOIN LookupTable l ON l.ID = a.FK AND l.[Source] = x.[Source];
TRUNCATE TABLE #TableB
;WITH LookupTable AS (
SELECT [Source] = 'B', ID = ID_b, Col1
FROM #TableB
UNION ALL
SELECT [Source] = 'C', ID = ID_c, Col1
FROM #TableC)
SELECT a.*, l.Col1
FROM #TableA a
CROSS APPLY(SELECT [Source] = CASE WHEN EXISTS (SELECT 1 FROM #TableB) THEN 'B' ELSE 'C' END) x
INNER JOIN LookupTable l ON l.ID = a.FK AND l.[Source] = x.[Source];
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply